How to LEFT JOIN while using a filter on the LEFT table?

Question

I have 2 tables that I want to LEFT JOIN and filter the results on the LEFT table. The tables are: -product_table, with columns id and product_name -order_table, with columns id, product_id, product_order

I tried to select a row from the product table where the id was 1 and LEFT JOIN the order table where the product id was the same as the id in the product table.

My first try was like this:

SELECT * FROM product_table 
WHERE product_table.id = 1
LEFT JOIN order_table
ON order_table.product_id=product_table.id

For this query I got a syntax error.

So I searched how I could LEFT JOIN and filter the results from the LEFT table and I found to use the AND after LEFT JOIN in the query, like this:

SELECT * FROM product_table
LEFT JOIN order_table
ON order_table.product_id=product_table.id
AND product_table.id=1

But here the query returns all the product_table rows and all the corresponding order_table rows, all joined, but all I want is just the product_table row where the id equals 1 and to be joined with the corresponding order_table row. How can I do this?

your query looks fine. Can you show a small data sample for products and orders? — rene, Dec 28 '11 at 10:09
Don't use [`SELECT *`](http://stackoverflow.com/questions/321299/) unless you're writing a DB administration program; select only the columns you need. — outis, Dec 28 '11 at 10:17

score 4 · Accepted Answer · edited Dec 30 '11 at 04:36

4

Try this:

SELECT * 
  FROM product_table pt 
    LEFT JOIN order_table ot 
      ON ot.product_id=pt.id
  WHERE pt.id = 1

edited Dec 30 '11 at 04:36

outis

75,655
22
151
221

answered Dec 28 '11 at 10:07

Leigh Ciechanowski

1,297
17
33

can you explain please? i cant understand what the pt and ot are there for. – nope Dec 28 '11 at 10:11
Sorry they are just Alias's for the tables so I didn't have to type the full table names the following should also work SELECT * FROM product_table LEFT JOIN order_table ON order_table.product_id=product_table.id WHERE product_table.id = 1 – Leigh Ciechanowski Dec 28 '11 at 10:13
+1: the first query of the OP would be correct if the where clause came after the join. – JB Nizet Dec 28 '11 at 10:17
pt and ot are the alias of the tables, used in the query the aliasing is very useful to reduce the complexity of the table names. – Punit Dec 28 '11 at 10:18

score 1 · Answer 2 · answered Dec 28 '11 at 10:14

You should to re write your query like this :=

SELECT * FROM product_table
LEFT JOIN order_table
ON order_table.product_id=product_table.id
AND product_table.id=1
where product_table.id=1

If you not use where clause then the all records are fetched from your product_table with matching condition placed in on clause.

score 0 · Answer 3 · answered Dec 28 '11 at 10:16

0

If you take a closer look at the syntax for SELECT, you'll see that the WHERE clause must come after the JOIN clause.

answered Dec 28 '11 at 10:16

outis

75,655
22
151
221

How to LEFT JOIN while using a filter on the LEFT table?

3 Answers3