Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

This question is marked "community wiki".

asked 14 Jun '12, 10:11

RDPSispetro's gravatar image

RDPSispetro
895610
accept rate: 0%

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/

(14 Jun '12, 14:44) Breck Carter
Replies hidden

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

(14 Jun '12, 14:59) RDPSispetro
3

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

(15 Jun '12, 07:48) Graeme Perrow

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
permanent link

answered 14 Jun '12, 12:52

Glenn%20Paulley's gravatar image

Glenn Paulley
10.8k576106
accept rate: 43%

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

(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
showing 3 of 6 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×25
×12

question asked: 14 Jun '12, 10:11

question was seen: 2,480 times

last updated: 15 Jun '12, 07:48