Complex Query - Distance between cities

[Former Member]

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

[Breck_Carter]

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/

[Former Member]

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

[graeme_perrow]

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

[Former Member]

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

[Breck_Carter]

'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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[VolkerBarth]

@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.)

[Former Member]

No preference, really, Volker.