MySQL returns rows that reference the same table

Question

Let's say I have a table that looks something like this:

+----+--------------+-------+----------------+------------+ | id | product_name | price | bulk_reference | bulk_count | +----+--------------+-------+----------------+------------+ | 1 | xxxx | 11.99 | 0 | 0 | +----+--------------+-------+----------------+------------+ | 2 | zzzz | 22.99 | 0 | 0 | +----+--------------+-------+----------------+------------+ | 3 | | | 2 | 10 | +----+--------------+-------+----------------+------------+ 

I can select all products, etc., without any problems. However, I need to return all the products, but the lines WHERE bulk_reference > 0 should return the reference values ​​of the strings for product_name and price that are not specified in the string ... In the same result set.

So, for example, my result set should look something like this:

 [0] => [id] = 1 [product_name] = xxxx [price] = 11.99 [bulk_reference] = 0 [bulk_count] = 0 [1] => [id] = 2 [product_name] = zzzz [price] = 22.99 [bulk_reference] = 0 [bulk_count] = 0 [2] => [id] = 3 [product_name] = zzzz [price] = 22.99 [bulk_reference] = 2 [bulk_count] = 10 

How can I do this only with MySQL?

Answer 1

I think this will be fine in your case:

 select P1.id, IF(bulk_reference>0,(select product_name from Products P2 where P2.id=P1.bulk_reference),P1.product_name) as product_name, IF(bulk_reference>0,(select price from Products P2 where P2.id=P1.bulk_reference), P1.price) as price, P1.bulk_reference, P1.bulk_count from Products P1; 

Here is the sqlfiddle link

Answer 2

That should do it. I don’t have the opportunity to check the syntax right now, but if you run into problems with the launch, I will review it later.

 select id , (if bulk_reference > 0 then (select p1.product_name from product p1 where p1.id = p3.bulk_reference) else product_name end if) , (if bulk_reference > 0 then (select p2.price from product p2 where p2.id = p3.bulk_reference) else price end if) , bulk_reference, bulk_count from products p3 

Answer 3

You can try this query:

 SELECT * FROM table WHERE bulk_reference = 0 UNION SELECT t1.id, t2.product_name, t2.price, t1.bulk_reference, t1.bulk_count FROM table t1 JOIN table t2 ON t2.id = t1.bulk_reference