need to link tables with different criteria

[ss7415]

want to link my tables on two different criteria's.

im getting an error with this, but you can see what im trying to do

select a,qad.VENDOR,qad.PO_NBR,qad.PO_ITEM_NBR,qad.lot_nbr,qad.DATE_RCV,qad.PART,qad.ord_PART,qad.PROJECT_ONLY,qad.WO_NBR,qad.VDESC,qad.QTY_ORIG,qad.QTY_OPEN,qad.RCVR,
qty_onhand,qty_last_rcv

from qad left outer join idf_table idf on
(if qad.part is not null and qad.lot_nbr is not null then (qad.part = idf.part) and (qad.lot_nbr = idf.lot_nbr))
(if qad.ord_part is not null and qad.wo_nbr is not null then (qad.ord_part = idf.part) and (qad.a = qad.lot_nbr))

 

[monksnake]

wouldn't that just be an inner join?

 

[monksnake]

Query like this:

select a,qad.VENDOR,qad.PO_NBR,qad.PO_ITEM_NBR,qad.lot_nbr,qad.DATE_RCV,qad.PART,qad.ord_PART,qad.PROJECT_ONLY,qad.WO_NBR,qad.VDESC,qad.QTY_ORIG,qad.QTY_OPEN,qad.RCVR,
qty_onhand,qty_last_rcv

from qad inner join idf_table idf on
qad.part = idf.part and qad.lot_nbr = idf.lot_nbr and
qad.ord_part = idf.part and qad.a = qad.lot_nbr

 

[monksnake]

I'm sorry I need to add this, assuming you have all the column names correct you would also need to add

and qad.wo_nbr is not null