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?

Answer 1

Try this:

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