Hi, I have 2 tables,
This question is marked "community wiki".
|
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 '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.
(14 Jun '12, 14:27)
Breck Carter
Replies hidden
Thanks a lot Glenn, It works exactly as I needed. Best Regards, Paulo Braga
(14 Jun '12, 14:56)
RDPSispetro
Replies hidden
Thanks for that..... I think.....
(14 Jun '12, 15:05)
Glenn Paulley
Thanks for the suggestion, Breck. I have communicated that suggestion to the doc team.
(14 Jun '12, 15:51)
Glenn Paulley
@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.)
(15 Jun '12, 03:15)
Volker Barth
Replies hidden
No preference, really, Volker.
(15 Jun '12, 06:52)
Glenn Paulley
|
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/
This is my first issue, I wasn't sure what this Comunity Wiki means..
FYI I have updated the FAQ to include a question on Community Wiki.