By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,374 Members | 2,993 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,374 IT Pros & Developers. It's quick & easy.

Join two columns to the same lookup table

P: 2
Hi guys,

I am having a hard time trying to create an SQL statement that will allow two columns in table A to both INNER JOIN with table B.

For example:
Expand|Select|Wrap|Line Numbers
  1. TableB:
  2. CityID       CityName
  3. 1            New York
  4. 2            Chicago
  5.  
  6. TableA:
  7. FlightID      Depart        Destination
  8. 101           2             1
  9.  
  10. Desired Result:
  11. FlightID      Depart        Destination
  12. 101           Chicago       New York
How do I get around the fact that both the DEPART field and the DESTINATION field both need to be joined with TableB (so that (TableA.Depart = TableB.CityID) and (TableA.Destination = TableB.CityID)) ?

Any ideas? I'd imagine that I would have to use alias's somehow, but I'm not sure how to set it all up.

Thanks!
-Tom
Oct 5 '07 #1
Share this Question
Share on Google+
4 Replies


JKing
Expert 100+
P: 1,206
Hi, you could use subqueries to acheive this sort of result.

Expand|Select|Wrap|Line Numbers
  1. SELECT TableB.FlightID, (SELECT CityName FROM TableA, TableB WHERE TableB.Depart = TableA.CityID) AS Depart, (SELECT CityName FROM TableA, TableB WHERE TableB.Destination = TableA.CityID) AS Destination
  2. FROM TableB;
  3.  
Oct 5 '07 #2

nico5038
Expert 2.5K+
P: 3,072
Another solution will be to place TableA once and TableB twice in the query editor.
(Access will rename the second TableB into TableB_1)
Now the Depart can be JOINed with TableB and Destination can be JOINed with TableB_1.

Getting the idea ?

Nic;o)
Oct 6 '07 #3

P: 2
Thanks to both of you for your help!!

I ended up using that second technique (adding the same table 2x into the query editor). I had to adjust the relationships a little, but it worked great! I never knew you could do that!

Thanks again!
-Tom
Oct 16 '07 #4

nico5038
Expert 2.5K+
P: 3,072
Glad we could teach you a new trick :-)

Success with your application !

Nic;o)
Oct 16 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.