CODEWITHSUNDEEP
mysql
Grirg
Grirg

Reputation: 275

What kind of JOIN should I be using here?

I have a quite longer query but I simplified to this:

SELECT p.ref, pl.name
FROM tpv_products p
LEFT JOIN tpv_products_languages pl 
ON p.id = pl.tpv_products_id
WHERE pl.tpv_languages_id = 2;

These are the tables:

tpv_products:

id | ref
--------
1  | ref1
2  | ref2
3  | ref3

tpv_products_language:

tpv_languages_id | tpv_products_id | name
---------------------------------------------
1                | 1               | Rice
1                | 2               | Corn
1                | 3               | Milk
2                | 1               | Arroz

What I get with my query:

ref | name
----------
ref1| Arroz

What I was expecting: 

ref | name
----------
ref1| Arroz
ref2| NULL
ref3| NULL

I've tried all type of JOINS but can't find the desired result.

Upvotes: 1

Views: 31

Answers (1)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Just add condition with join pl.tpv_languages_id = 2

SELECT p.ref, pl.name
FROM tpv_products p
LEFT JOIN tpv_products_languages pl 
ON p.id = pl.tpv_products_id AND pl.tpv_languages_id = 2;

Upvotes: 2

Related Questions