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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

TiberiuGalCommented:
I guess you need to use the where clause on both your selects within the union,
0
Kent OlsenDBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.