By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,432 Members | 795 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,432 IT Pros & Developers. It's quick & easy.

joining multiple tables based on 1 criteria

P: 31
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!
Jul 31 '08 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
What's in AUDIT? Positive or Negative? or both?

-- CK
Jul 31 '08 #2

P: 31
both. So I want to see what item numbers are in AUDIT and match them to the master and retail records accordingly.
Jul 31 '08 #3

ck9663
Expert 2.5K+
P: 2,878
Use left join on both, instead of inner join

-- CK
Jul 31 '08 #4

P: 3
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
Aug 4 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.