phpmyadmin Question posted in
The official documentation can be found here.

SQL Two result from one column in 2 columns – Phpmyadmin

i have checked some similar issues in the forum but i can’t seems to get it to work properly.
i’m on phpmyadmin

i need to get a result like that :

Reference | ProductNameEnglish | ProductNameFrench

What’s blocking me is to do 2 requests on the same column (pl.name) :/

Here is my query for now :

SELECT
p.reference AS Reference,

(SELECT pl.name
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
WHERE p.active = 1
AND pl.id_lang = 2) AS ENname,

(SELECT pl.name
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product)
WHERE p.active = 1
AND pl.id_lang = 1) AS FRname

FROM ps_product p
Tags:

2

Answers


  1. 0

    You don’t need the join in the subqueries:

    SELECT p.reference AS Reference,
       (SELECT pl.name
        FROM ps_product_lang pl 
        WHERE p.id_product = pl.id_product AND
              p.active = 1 AND
              pl.id_lang = 2
       ) AS ENname,
       (SELECT pl.name
        FROM ps_product_lang pl 
        WHERE p.id_product = pl.id_product AND
              p.active = 1 AND
              pl.id_lang = 1
       ) AS FRname
FROM ps_product p;

    This assumes that only one row matches the subqueries. You may need to limit the results to a single row if that is not the case.

    Login or Signup to reply.
  2. 0 
    SELECT
  p.reference AS Reference,
  pl.name as ENname,
  pf.name as FRname
FROM ps_product p
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product and pl.id_lang = 1)
LEFT JOIN ps_product_lang pf ON (p.id_product = pf.id_product and pf.id_lang = 2)
WHERE p.active = 1

    I might have switched English and French, but that should be easy to fix….

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search