Grirg Grirg - 4 months ago 7
MySQL Question

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.

Answer

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;
Comments