Many thanks Peter. That's the definition I was after. Kerry -----Original Message----- From: Peter K AGANYO [mailto:[EMAIL PROTECTED] Behalf Of Peter K AGANYO Sent: 19 February 2007 00:35 To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: SELECT single row from 2 tables with WHERE clause
Hi Kerry, Try this: SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.linkfield=t2.linkfield WHERE t1.lookup='ZZZZZ' Without the WHERE condition this would return all 1000 rows of table 1 since "A Left join returns all rows of the left of the conditional even if there is no right column to match." but t1.lookup='ZZZZZ' constrains this to only the one row of table one with lookup equal to 'ZZZZZ'. Enjoy Peter On 2/17/07, Kerry Frater <[EMAIL PROTECTED]> wrote: I am trying to select a particular row from a table and include a column for aq second table but I cannot get the result I am after. I have "table1" with 1000 rows and "table2" with 12 rows. The relationship between the tables is a column "linkedfield". Table1 has a unique key called "lookup" If I use the code SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='ZZZZZ') I get a result of 12 rows (as expected) SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.linkfield=t2.linkfield) I get 1000 rows as expected SELECT t1.*,t2.thedescription AS t2desc FROM table1 AS t1, table2 AS t2 where (t1.lookup='ZZZZZ') and (t1.linkfield=t2.linkfield) returns 1 row if there is an active link between the two tables and returns 0 rows if there isn't. This is where I am stuck. In the last example I would like the 1 row whether there is an "active" link or not. The difference will be simply that the "t2desc" rsulting column will be blank or contain a value. Can anyone help me with the logic? Kerry