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='Z'
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='Z' constrains this to
only the one row of table one with lookup equal to 'Z'.
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='Z')
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='Z') 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
