List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:June 28 2003 9:42pm
Subject:Re: left join on more than one field
View as plain text  
At 12:29 -0700 6/28/03, Eric Kilgore wrote:
>Anyone know how to perform a left join by more than one field?
>
>I have several tables I'm working with. An example query is like this:
>
>SELECT tbl1.Name, tbl1.Location, tbl1.Case,  tbl1.Class, tbl1.Type,
>tbl3.Identity, tbl2.FileDate, tbl2.Status
>FROM tbl2, tbl1 LEFT JOIN tbl3 ON tbl1.Case = tbl3.Case
>WHERE tbl1.Case = tbl2.Case AND tbl1.Name LIKE '$Name%'
>
>This returns everything I need, except that it also returns rows in tbl3
>that I don't want (there can be multiple rows for the same Case). So I need
>to limit by tbl1.Party = tbl3.Party as well.
>
>If I add this to my where clause I defeat the purpose of the left join for
>the null entries in table 3 and I don't know how to create two left join
>statements for the same table. I know this is possible with many different
>tables but found no info on two fields from the same two tables.

Add the condition to your ON clause:

ON tbl1.Case = tel3.Case AND tbl1.Party = tbl3.Party

>
>Any help would be appreciated.
>
>Eric


-- 
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

Are you MySQL certified?  http://www.mysql.com/certification/

Thread
left join on more than one fieldEric Kilgore28 Jun
  • Re: left join on more than one fieldPaul DuBois28 Jun
RE: left join on more than one fieldEric Kilgore29 Jun