Home / Table Joins / Question No: 18

I am trying to select a particular row joining two tables 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);

The above query returns one row as desired if there is an active record between the two tables otherwise returns 0 row if there is not. This is where I am stuck. In the above example query, I would like the 1 row whether there is an "active" link or not. Can anyone help me with the logic?

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 table1 since "a LEFT JOIN returns all rows of the left of the condition 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'.