SQL JOIN Question for MySQL

["Shannon ODonnell" <sodonnell@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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 where those three keys match from the first
table.and because I want it to select the rows from the first table even if
there are no matches in the second table, I'm using an OUTER JOIN (Rather
than an inner Join).

 

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'

 

 

 

 

 

 

 

 

Shannon O'Donnell