BOTJr. - 1 year ago 66
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

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.

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.

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)))
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download