 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2006Easy(?) SQL question
I have two tables table1: id state ----------------- 10 washington 11 oregon 12 california table2: product from to -------------------------- piano 10 11 guitar 12 10 piano 12 11 and now I want to do a select-case that returns ------------- piano washington oregon piano california oregon but I can't find out how. I have tried SELECT id, state FROM table1, table2 WHERE table2.product = 'piano' and table2.from = table1.state but I cant figure out how to get the "to" state as well! Best Regards Eric >I have two tables > [quoted text clipped - 27 lines] > Best Regards > Eric Not the only way, but you could do this: SELECT P.Product, S1.State AS FromState, S2.State AS ToState FROM (table2 AS P INNER JOIN table1 AS S1 ON P.from=S1.id) INNER JOIN table1 AS S2 ON P.to=S2.id WHERE P.product='piano' Of course, if the from and to columns in table2 might be missing, then replace INNER JOIN with LEFT JOIN. Also you should avoid reserved words (such as 'from') for your field names. >I have two tables > [quoted text clipped - 27 lines] > Best Regards > Eric Not the only way, but you could do this: SELECT P.Product, S1.State AS FromState, S2.State AS ToState FROM (table2 AS P INNER JOIN table1 AS S1 ON P.from=S1.id) INNER JOIN table1 AS S2 ON P.to=S2.id WHERE P.product='piano' Of course, if the from and to columns in table2 might be missing, then replace INNER JOIN with LEFT JOIN. Also you should avoid reserved words (such as 'from') for your field names. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.