BOTJr. BOTJr. - 1 month ago 9
MySQL Question

union of every row in tables

I really dont know what should be the title but i will try to explain it as clearly as possible.

I have an online food portal which includes various kinds of foods.Depending on the food , i characterize different attributes for different food.
For the sake of simplicity, assume that i sell cakes and wraps.Cakes can have the following attributes i.e they have weight and depending on the weights their prices vary.

Similarly,wraps can have two attributes , they can be large and small.

base_product_table
ID | product_code | product name | company_id
1 | 12345 | beer cake |343434defee
2 | 12346 | vodka cake |343434deereee
3 | 123451 | cheese wrap |wqeqwewwe
4 | 123453 | nb wrap |weqwew
5 | 123464 | ad wrap | adwadw

cake_table
Product_code |Quantity | price | weight
12345 | 34 |345 |0.5
12345 | 343 |600 |1.0
12345 | 4 |845 |1.5
12346 | 341 |345 |0.5

wraps_table
Product_code |Quantity | price | size
123451 | 34 |345 |s
123451 | 343 |600 |l
123453 | 4 |845 |s
123464 | 341 |345 |s


These are my three tables , one being base table while the other two can be joined with the base table to get the desired table.

The real problem with this method comes up when i have to display every product on my products page.I could have simply done
select * from base_product_table
but i need to display the product price with the product weight or size ,if present.

enter image description here
The above image shows what i meant by displaying products on the product page.

How can i achieve my solution ?

Update:
In cake table i have a cake but with three different weights then the output should be only one for that respective cake with weight being the smallest weight and the price of that smallest weight.

Similarly, wraps has two size Small and large , output will be only one for that wrap with the smaller record price.

Answer

This SQL returns the price for cakes, assuming you want the price for the smallest (in weight).

SELECT pt.product_name,
       pt.company_id,
       (SELECT MIN (price)
          FROM cake_table ct
         WHERE     ct.product_code = pt.product_code
               AND ct.weight = (SELECT MIN (sub.weight)
                                  FROM cake_table sub
                                 WHERE sub.product_code = ct.product_code))
           cake_price
  FROM base_product_table pt

If this works, than as previosly suggested, it's a matter of just joining the two tables:

( (SELECT pt.product_name,
          pt.company_id,
          (SELECT MIN (price)
             FROM cake_table ct
            WHERE     ct.product_code = pt.product_code
                  AND ct.weight = (SELECT MIN (sub.weight)
                                     FROM cake_table sub
                                    WHERE sub.product_code = ct.product_code))
              cake_price
     FROM base_product_table pt
    WHERE EXISTS
              (SELECT 'X'
                 FROM cake_table ck
                WHERE ck.product_code = pt.product_code))
 UNION ALL
 (SELECT pt.product_name,
         pt.company_id,
         (SELECT MIN (price)
            FROM wraps_table ct
           WHERE     ct.product_code = pt.product_code
                 AND ct.weight = (SELECT MIN (sub.weight)
                                    FROM wraps_table sub
                                   WHERE sub.product_code = ct.product_code))
             cake_price
    FROM base_product_table pt
   WHERE EXISTS
             (SELECT 'X'
                FROM wraps_table ck
               WHERE ck.product_code = pt.product_code)))