× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



I got the solution from another source but I'm going to post it here for the
archives.


This is the correct syntax and returns the correct # of rows (1 in this
case).

SELECT * FROM xcitations LEFT 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'

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'








-----Original Message-----
From: pctech-bounces@xxxxxxxxxxxx [mailto:pctech-bounces@xxxxxxxxxxxx] On
Behalf Of Shannon ODonnell
Sent: Wednesday, April 28, 2010 2:58 PM
To: 'PC Technical Discussion for iSeries Users'
Subject: [PCTECH] SQL JOIN Question for MySQL

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




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.