[FIXED] How to get 2 columns from one table and 2 rows as columns from other table in one row, in MySQL?

Issue

I know this is quite complicated, but I sincerely hope someone will check this out. I made short version (to better understand the problem) and full version (with original SQL)

Short version:

[TABLE A] [TABLE B]
|1|a|b|   |1|x
|2|c|d|   |1|y
|3| | |   |2|z
|5| | |   |2|v
          |4|w

How can I make MySQL query to get rows like that:

1|a|b|x|y
2|c|d|z|v

2 columns from A and 2 rows from B as columns, only with keys 1 and 2, no empty results

Subquery?

Full version:

I tried to get from Prestashop db in one row:

• product id
• ean13 code
• upc code
• feature with id 24
• feature with id 25

It's easy to get id_product, ean13 and upc, as it's one row in ps_product table. To get features I used subqueries (JOIN didn't work out).

So, I selected id_product, ean13, upc, (subquery1) as code1, (subquery2) as code2. Then I needed to throw out empty rows. But couldn't just put code1 or code2 in WHERE. To make it work I had to put everything in subquery.

This code WORKS, but it is terribly ugly and I bet this should be done differently.

How can I make it BETTER?

SELECT * FROM(
    SELECT 
        p.id_product as idp, p.ean13 as ean13, p.upc as upc, (
            SELECT
                fvl.value
            FROM
                `ps_feature_product` fp
            LEFT JOIN
                `ps_feature_value_lang` fvl ON (fp.id_feature_value = fvl.id_feature_value)
            WHERE fp.id_feature = 24 AND fp.id_product = idp
        ) AS code1, (
            SELECT
                fvl.value
            FROM
                `ps_feature_product` fp
            LEFT JOIN
                `ps_feature_value_lang` fvl ON (fp.id_feature_value = fvl.id_feature_value)
            WHERE fp.id_feature = 25 AND fp.id_product = idp
        ) AS code2,
        m.name
    FROM 
        `ps_product` p 
    LEFT JOIN 
        `ps_manufacturer` m ON (p.id_manufacturer = m.id_manufacturer)
) mainq
WHERE 
    ean13 != '' OR upc != '' OR code1 IS NOT NULL OR code2 IS NOT NULL

Solution

create table tablea 
( id int,
  col1 varchar(1),
  col2 varchar(1));

create table tableb 
( id int,
  feature int,
  cola varchar(1));

insert into tablea (id, col1, col2)
select 1,'a','b'  union
select 2,'c','d'  union
select 3,null,null  union
select 5,null,null;


insert into tableb (id, feature, cola)
select 1,24,'x'  union
select 1,25,'y' union
select 2,24,'z' union
select 2,25,'v' union
select 4,24,'w';

select a.id, a.col1, a.col2, b1.cola b1a, b2.cola b2a
from tablea a
inner join tableb b1 on (b1.id = a.id and b1.feature = 24)
inner join tableb b2 on (b2.id = a.id and b2.feature = 25);

SQLFiddle here.

Answered By - James Carroll

Answer Checked By - David Marino (PHPFixing Volunteer)