WHERE specification with 2 refs

[UKBob]

Hey people, I've got a quick question regarding a problem I've come across writing an SQL query.

I've got two tables, one with various fields including say Country of Origin ID and Country of Destinination ID. And another table with two fields, 1) Country ID and 2) Country name. The idea is that when displaying data from the first table you look up the country names in the the second table using the ID number.

How exactly do I specify the WHERE statement for looking up both Country fields? Because once you specify that Country ID = Country of Origin ID, I don't see how you can then say that Country ID = Country of Destination ID (which would only return entries where the two IDs are equal). My guess is that I will need to use the AS statement somewhere. Hope one of you can help.

 

[PHV]

You have to play with 2 instances of the country table:
SELECT CountryOfOriginID, O.CountryName, CountryOfDestinationID, D.CountryName
FROM yourTable
INNER JOIN tblCountry O ON CountryOfOriginID = O.CountryID
INNER JOIN tblCountry D ON CountryOfDestinationID = D.CountryID

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

 

[UKBob]

That was quick. Thanks very much.