MySQL OUTER JOIN Syntax Problem

Hi,

Trying to figure out the syntax for an SQL Outer Join over a MySQL database.


So I have two tables in MySQL.    The tables have 3 common keys:

XmittalBatchNum
CaseID
CitationID


Those keys exist in both tables.


My goal is to select all records that match my WHERE clause,  and I need to get the row(s) from the first table even if there is not a match in the second table.  So I used an OUTER JOIN which I think is correct.  

When I run the following,  I should get one row back
.  Instead, I’m getting something like 35,000+ rows and I’m not sure why.  One problem I know is that I am only specifying the join on the first key, XmittalBatchNum.  But I can’t figure out the syntax to add in the condition to check for the other two keys.

Anyway…here is my SQL.    I can’t figure this out.   Can someone please offer a suggestion or two on this?

Thanks!

SELECT *  FROM xcitations
      LEFT JOIN xcitationbonds ON xcitations.XmittalBatchnum=xcitationbonds.XmittalBatchNum  


UNION


SELECT * FROM xcitations
      RIGHT JOIN xcitationbonds ON xcitations.XmittalBatchNum = xcitationbonds.XmittalBatchNum

 WHERE xcitations.XmittalBatchNum = '-100-20100303'
     AND xcitations.CaseID ='88eac5f3-793d-467e-af15-f98c5bf57df8'
          AND xcitations.CitationID = '4804'

TURBOSHANAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi Turbo,

You're getting so many rows because you're joining on only one of your 3 keys.  Every key in tableA with key value 'x' will join to every row in tableB with key value 'x'.  If there are 20 'x' keys in tableA and 40 in tableB you've just created 800 rows.  :)

Just include an AND condition on the join:

SELECT * FROM xcitations
      RIGHT JOIN xcitationbonds
      ON xcitations.XmittalBatchNum = xcitationbonds.XmittalBatchNum
      AND xcitations.CaseID = xcitationbonds.CaseID
      AND xcitations.CitationID = xcitationbonds.CitationID
WHERE .....


Good Luck,
Kent

0
 
TiberiuGalCommented:
Hi,
you can join on multiple fields using parentheses:

SELECT *  FROM xcitations 
      LEFT JOIN xcitationbonds ON ( xcitations.XmittalBatchnum=xcitationbonds.XmittalBatchNum  
xcitations.CaseID =xcitationbonds.CaseID  
xcitations.CitationID =xcitationbonds.CitationID
)
WHERE ....

Open in new window

0
 
TURBOSHANAuthor Commented:
That was VERY! close!   Now I'm only getting 880 rows, which is the totatl # of rows in the first table.  

So I'm still missing something...   Here's what it looks like now, using your suggestion:

  SELECT *  FROM xcitations LEFT JOIN xcitationbonds
        ON xcitations.XmittalBatchnum=xcitationbonds.XmittalBatchNum
           AND xcitations.CaseID = xcitationbonds.CaseID
             AND xcitations.CitationID = xcitationbonds.CitationID

UNION

SELECT * FROM xcitations RIGHT JOIN xcitationbonds
    ON xcitations.XmittalBatchNum = xcitationbonds.XmittalBatchNum  
       AND xcitations.CaseID = xcitationbonds.CaseID
         AND xcitations.CitationID = xcitationbonds.CitationID

WHERE xcitations.XmittalBatchNum = '-100-20100303'
       AND xcitations.CaseID ='88eac5f3-793d-467e-af15-f98c5bf57df8'
           AND xcitations.CitationID = '4804'
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
TiberiuGalConnect With a Mentor Commented:
I guess you need to use the where clause on both your selects within the union,
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Turbo,

The UNION operator will filter out duplicates, if there are any.  Perhaps you want to use UNION ALL there?

And yes, you'll want in include a WHERE filter on every subquery.


Kent
0
 
TURBOSHANAuthor Commented:
TiberiuGal:  That was it!    I didn't even know that was allowed to use the Where clause twice like that.

Very interesting!!!

I'm awarding the points 250 and 250 for both of you beacause I used both of your solutions.

THANKS AGAIN!!!
0
 
TURBOSHANAuthor Commented:
Love Experts-Exchange!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.