CODEWITHSUNDEEP
mysqljoinfilter
nope
nope

Reputation: 1078

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

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?

Upvotes: 4

Views: 9410

Answers (3)

Leigh Ciechanowski
Leigh Ciechanowski

Reputation: 1317

Try this:

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

Upvotes: 4

outis
outis

Reputation: 77450

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

Upvotes: 0

Bajrang
Bajrang

Reputation: 8639

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.

Upvotes: 1

Related Questions