[SOLVED] SQL query LEFT JOIN two tables with WHERE clause for the second table's column

SQL query LEFT JOIN two tables with WHERE clause for the second table's column

Table1: Id1, Phrase
Data:
  1   Hello
  2   Bye
  3   Thanks

Table2: Id2, Color, Shape
Data:
  1   Green     Square
  3   Yellow    Circle
  3   Blue      Square

The requirement for the query is to return ALL rows from Table1 left joined with Table2 where Shape = 'Square', i.e.

1   Hello   Green
2   Bye     NULL
3   Thanks  Blue

Query 1:

SELECT Id1, Phrase, Color
FROM Table1
LEFT JOIN Table2 ON Id2 = Id1

would return

1   Hello   Green
2   Bye     NULL
3   Thanks  Yellow
3   Thanks  Blue

Query 2:

SELECT Id1, Phrase, Color
FROM Table1
LEFT JOIN Table2 ON Id2 = Id1
WHERE Shape = 'Square'

would return

1   Hello   Green
3   Thanks  Blue

How can I return what's required?

Solution

Your second query is almost correct, but the restriction in the WHERE clause needs to be moved to the ON clause of the join:

SELECT t1.Id1, t1.Phrase, t1.Color
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t2.Id2 = t1.Id1 AND
       t2.Shape = 'Square';

But phrasing the query this way, no rows from the first table are filtered off, even if there be no matching records in the second table. Your current second query completely filters off any records not matching the shape requirement.