joining multiple tables based on 1 criteria

[Cyprus106]

Apparently, Im incapable of properly executing this query! I've been at this for far too long and gotten nowhere. Forgive me if this is a umb mistake; I'm not great at SQL.

I've got three tables, MASTER, RETAIL, and AUDIT... MASTER and RETAIL have the same fields, except each record has a unique ITEMNO, and master's numbers are positive and retail's are negative. AUDIT contains some of both table's item numbers in it. I just want to join audit to both tables based on the ITEMNO, (Audit's ITEMNO field is actually called MMNO, if that matters) I can JOIN one or the other and it'll show up with the right fields, but when I try to join both it returns with no records. I know I'm messing up, I just don't know how to fix it.

This is what I've got (take out either master OR retail and it works fine):
SELECT AUDIT.TRANSACTIONNO,AUDIT.DATIM, AUDIT.MMNO, AUDIT.COPYS, AUDIT.PRICE,
MASTER.ITEMNO, MASTER.MTITLE, MASTER.MSRP,
RETAIL.ITEMNO, RETAIL.MTITLE, RETAIL.MSRP,
FROM BUYBACKAUDIT
JOIN RETAIL ON AUDIT.ITEMNO=RETAIL.MMBSNO
JOIN MASTER ON AUDIT.ITEMNO=MASTER.MMBSNO

Any help would be much appreciated. Thanks!

[ck9663]

What's in AUDIT? Positive or Negative? or both?

-- CK

[Cyprus106]

both. So I want to see what item numbers are in AUDIT and match them to the master and retail records accordingly.

[ck9663]

Use left join on both, instead of inner join

-- CK

[snathanm]

Hi

use the following query

SELECT AUDIT.TRANSACTIONNO,AUDIT.DATIM, AUDIT.MMNO, AUDIT.COPYS, AUDIT.PRICE,
MASTER.ITEMNO, MASTER.MTITLE, MASTER.MSRP,
RETAIL.ITEMNO, RETAIL.MTITLE, RETAIL.MSRP,
FROM BUYBACKAUDIT AUDIT
LEFT OUTER JOIN RETAIL ON AUDIT.ITEMNO=RETAIL.MMBSNO
LEFT OUTER JOIN MASTER ON AUDIT.ITEMNO=MASTER.MMBSNO

Thanks
Nathan
Hyper Drive
Bangalore