[SOLVED] embeded Selects in Select or inner join

[QuickBooksDev]

I need a Select statement to get multiple sub selects

 

It is basically using 2 keys for State Code to lookup the real values (State abv) in a State table.

 

Given Orders, Products, States

There are multiple rows in Products for each Orders, using and Id key so

SELECT * FROM Orders, Products WHERE Orders.id = Products.id

works fine.

 

I now need to get the State Abv. from States from 2 keys from Orders

i.e. SELECT State.code from States, TableA WHERE Orders.key1 = State.key

AND ALSO

SELECT State.code from States, Orders WHERE Orders.key2 = States.key

 

I have tried

SELECT * FROM Orders, TableB WHERE TOrders.id = TableB.id,

(SELECT States.code from TableC, Orders WHERE Orders.key2 = States.key) as CODE1, (SELECT States.code from States, Orders WHERE Orders.key2 = States.key) as CODE2

WHERE Orders.id = Products.id

 

But that gives me an excess amount of rows (800 vs 11).

 

Any help would be appreciated.

 

 

[corbin]

Sounds like you might be looking for a JOIN.

[QuickBooksDev]

I have tried but can't get it to work.  SQL systex errors.

 

Can you provide a sample?

[QuickBooksDev]

I got it.

 

I thought the inner joins would work but it was trial and error.

 

Tanx and Happy Tanx