SQL Nested Query

[GORAVENS]

How would I convert the following three queires into one nested query

SELECT T1.ITEM
FROM TABLE1 T1
WHERE T1.NUMBER = 123

SELECT T2.VALUE
FROM TABLE2 T2
WHERE T2.ITEM = T1.ITEM

SELECT T3.LOCATION
FROM TABLE3 T3
WHERE T3.ITEM = T2.ITEM

 

[swampBoogie]

SELECT T3.LOCATION
    FROM TABLE3 T3
 WHERE T3.ITEM in (
SELECT T2.item
    FROM TABLE2 T2
 WHERE T2.ITEM in (
SELECT T1.ITEM
    FROM TABLE1 T1
 WHERE T1.NUMBER = 123))

Should it be item or value in T2?

 

[GORAVENS]

Thanks SWAMP man, tried w/o success

 

[rrrkrishnan]

Is this what you are looking for.

SELECT T1.ITEM,T2.VALUE,T3.LOCATION
FROM TABLE1 T1, TABLE2 T2, TABLE3 T3
WHERE T1.NUMBER = 123
AND T2.ITEM = T1.ITEM
AND T3.ITEM = T2.ITEM

 

[rrrkrishnan]

OR:

SELECT T1.ITEM,T2.VALUE,T3.LOCATION
FROM TABLE1 T1 JOIN TABLE2 T2 ON T2.ITEM = T1.ITEM JOIN TABLE3 T3 ON T3.ITEM = T1.ITEM
WHERE T1.NUMBER = 123

 

[swampBoogie]

Goravens:

In what sense does it not work? Error messages? Unexpected result?

 

[nicsin]

What about using joins?

SELECT T1.ITEM, T2.VALUE, T3.LOCATION
FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.ITEM = T2.ITEM
INNER JOIN TABLE3 T3 ON T2.ITEM = T3.ITEM
WHERE T1.NUMBER = 123

 

[dnoeth]

Wow, nicsin successfully added the optional INNER keyword to rrrkrishnan's query.
Who's going to award a star for that?

Dieter

 

[nicsin]

rrrkrishnan,

sorry for crossposting. I failed to see your second post.


dnoeth,

no comments.