Easy(?) SQL question

[e_hobsbawm@hotmail.com]

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

[Anthony England]

Re: Easy(?) SQL question

<e_hobsbawm@hot mail.com> wrote in message
news:1142264870 .879322.153370@ i39g2000cwa.goo glegroups.com.. .[color=blue]
>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[/color]


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='pian o'

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.

[Anthony England]

Re: Easy(?) SQL question


<e_hobsbawm@hot mail.com> wrote in message
news:1142264870 .879322.153370@ i39g2000cwa.goo glegroups.com.. .[color=blue]
>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[/color]


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='pian o'

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.