Question Re: Complex Query - Distance between cities in Technology Q&A

Complex Query - Distance between cities

Former Member — Thu, 14 Jun 2012 10:11:24 GMT

Hi,

I have 2 tables,
the first has columns SourceCodeCity, TargetCodeCity and Distance (there is no NameCity on it).
The second has columns CodeCity and NameCity.
I need a query to show a table almost like this..
NameCity(SourceCodeCity) | NameCity(TargetCodeCity) | Distance
Can you help-me ?
I tried with select and some joins but does not work

Re: Complex Query - Distance between cities

Former Member — Thu, 14 Jun 2012 12:52:13 GMT

Let's suppose the first table is called DISTANCES, the second table is called CITYNAMES.

You want something like

SELECT SOURCE.NameCity, TARGET.NameCity, DISTANCES.Distance
FROM DISTANCES, CITYNAMES AS SOURCE, CITYNAMES AS TARGET
WHERE DISTANCES.SourceCodeCity = SOURCE.CodeCity AND
DISTANCES.TargetCodeCity = TARGET.CodeCity

Re: Complex Query - Distance between cities

Breck_Carter — Thu, 14 Jun 2012 14:27:30 GMT

'Tis an old and valuable technique, using different correlation names to allow a table to be joined to itself in a FROM clause.

So old, in fact, that it got dropped from the docs somewhere between Version 5.5 and 12... well, not dropped, but moved to an obscure topic that implies you must use the WITH clause (you don't): http://dcx.sybase.com/index.html#1201/en/dbusage/ug-commontblexpr-s-3099894.html

Perhaps this sentence from the Version 5.5 docs should be added to the V12 topic on the FROM clause http://dcx.sybase.com/index.html#1201/en/dbreference/from-statement.html

SQL Anywhere User's Guide

PART 6. SQL Anywhere Reference

CHAPTER 43. Watcom-SQL Statements

FROM clause

The correlation name is also necessary to distinguish between table instances when referencing the same table more than once in the same query.

Re: Complex Query - Distance between cities

Breck_Carter — Thu, 14 Jun 2012 14:44:32 GMT

It's a good question, but... apparently there is an unintended consequence to marking it "community wiki": you don't get points when someone votes "up".

Maybe you don't care about the points, but... you do need points to do some things: http://sqlanywhere-forum.sap.com/faq/

Re: Complex Query - Distance between cities

Former Member — Thu, 14 Jun 2012 14:56:50 GMT

Thanks a lot Glenn,

It works exactly as I needed.
About your observation, and seeing your photo.
We use to say here, "God gives hair to some people and brains to others". (lol)
Thanks for sharing your vast knowledge.

Best Regards,

Paulo Braga
Curitiba - Brazil

Re: Complex Query - Distance between cities

Former Member — Thu, 14 Jun 2012 14:59:12 GMT

This is my first issue, I wasn't sure what this Comunity Wiki means..

Re: Complex Query - Distance between cities

Former Member — Thu, 14 Jun 2012 15:05:10 GMT

Thanks for that..... I think.....

Re: Complex Query - Distance between cities

Former Member — Thu, 14 Jun 2012 15:51:37 GMT

Thanks for the suggestion, Breck. I have communicated that suggestion to the doc team.

Re: Complex Query - Distance between cities

VolkerBarth — Fri, 15 Jun 2012 03:15:33 GMT

@Glenn: Wouldn't you prefer a JOIN condition instead of a WHERE clause here, such as

SELECT SOURCE.NameCity, TARGET.NameCity, DISTANCES.Distance
FROM DISTANCES
  INNER JOIN CITYNAMES AS SOURCE ON DISTANCES.SourceCodeCity = SOURCE.CodeCity
  INNER JOIN CITYNAMES AS TARGET ON DISTANCES.TargetCodeCity = TARGET.CodeCity

(Yes, the outcame is surely identical here.)

Re: Complex Query - Distance between cities

Former Member — Fri, 15 Jun 2012 06:52:08 GMT

No preference, really, Volker.

Re: Complex Query - Distance between cities

graeme_perrow — Fri, 15 Jun 2012 07:48:17 GMT

FYI I have updated the FAQ to include a question on Community Wiki.