SQL Question: "Double" join 1

[rudejohn]

Here's the scenario:

"Table 1" has INFO and SOURCE and DESTINATION address
"Table 2" has address and name (essentially a lookup table)

I need a SQL query that will give me:
INFO SOURCE_NAME DESTINATION_NAME

Essentially, I need to lookup the name for both the SOURCE and DESTINATION address. Here's what I have, and it's not working:

SELECT A1.Info, A2.Name DESTINATION, A2.Name SOURCE
FROM ExportData A1, Addresses A2
WHERE A2.Address = A1.Destination, A2.Address = A1.Source;

Thanks in advance!


************
RudeJohn
************

 

[againstTheWind]

try this:

WHERE A2.Address = A1.Destination
AND A2.Address = A1.Source;

You need to include AND between the join conditions in the WHERE clause.

Good luck!

 

[rudejohn]

Thanks, I'll try that immediately.

That will work even though the Destination and Source addresses are different?

************
RudeJohn
************

 

[rudejohn]

Didn't work. When I put the "AND" in I didn't get any results, and if I make it "OR" the DESTINATION and SOURCE address are the same (which I know from the table they are not).

Any suggestions?

************
RudeJohn
************

 

[againstTheWind]

Can you give us some sample data?

 

[rudejohn]

Sure. I'm simplifying the data a bit to mask the application, but this is logically the same:

Table 1
INFO SOURCE DESTINATION
blue wacker main
yellow broadway 2nd
red main broadway
purple cleveland 2nd
white 2nd wacker
black jackson broadway

Table 2
ADDRESS NAME
wacker chicago
main miami
broadway dallas
2nd new york

I'd like the query to return a "table" that shows me the info for each pair of names, i.e.
INFO SOURCE_NAME DESTINATION_NAME
red miami dallas

************
RudeJohn
************

 

[PHV]

And something like this ?
SELECT A.Info, D.Name DESTINATION_NAMe, S.Name SOURCE_NAME
FROM (ExportData A
INNER JOIN Addresses D ON A.Destination = D.Address)
INNER JOIN Addresses S ON A.Source = S.Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rudejohn]

Thanks for your help. I'm getting the following error message:

Microsoft Access: Join Not Supported.

************
RudeJohn
************

 

[rudejohn]

Oops! Found a typo in my code. Thanks for your help, excellent detective work!
RJ

************
RudeJohn
************