Sql Join Query Help

[VZmike]

Hey guys, in a pinch with this one. I need assistance trying to figure out this query where I need to join 3 different tables.

Table 1: xORDERS
Table 2: xINVENTORY
Table 3: xSUPPLY

I'm trying to do a querry to translate two codes I have in xORDERS through a select statement.

in xORDERS there's the columns INVCD and SUPCD. Each are 6 digit codes, that match up to columns in xINVENTORY and xSUPPLY. In both of those tables, the column is titled xCODE.

What I want to do is say, take all of the entries in xORDERS, and for every INVCD code, go to xINVENTORY, find that same code and return the value of the column titled xLABEL. Also, for every SUPCD code, go to xSUPPLY, find that same code and return the value of the column titled xLABEL.

I can get one half of it to work, but it always errors when I try to get the second half.

I'm really pressed for time on this project, and my only hangup is this query. Any assistance would be awesome.

[erickwidya]

SELECT t1.INVCD, t1.SUPCD, (SELECT t2.xLABEL FROM xINVENTORY AS t2 WHERE t2.xCODE = t1.INVCD) AS xInventory,(SELECT t3.xLABEL FROM xSUPPLY AS t3 WHERE t3.xCODE = t1.SUPCD) AS xSupply
 ORDER BY t1.InVCD

if it's not working plz post the tables structure

[VZmike]

it worked, thanks

[cjard]

dont even need to use reduced result sets:

select
xORDERS.name,
xINVENTORY.name,
xSUPPLY.name
from
xORDERS,
xINVENTORY,
xSUPPLY
where
xORDERS.inventoryID = xINVENTORY.ID,
xORDERS.supplyID = xSUPPLY.ID



do a select * from, if you want to see the full thing.. the where joins all the tables togetehr on their IDs, one row per matching row, from the 3 tables..
then just 3 columns are picked out