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.
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/