CODEWITHSUNDEEP
mysqlsqlselect
Sven Slijkoord
Sven Slijkoord

Reputation: 35

MySql select products with multiple sub-products from 2 tables

I am trying to select products from an order in my webshop MySQL database.

This is the structure of the 2 tables I am trying to get information from

Table order_product:
| order_product_id | order_id | product_id | name___ | model | quantity | price_ | total_ |
| 63072_________| 41761__ | 4022____ | Product 1 | P001_| 2______| 23.95 | 47.90 |

Table of order_product_option:
| order_id | order_product_id | product_product_id | name__ | price |
| 41761__| 63072_________| 4022____________| option 1 | 0.90_|
| 41761__| 63072_________| 4022____________| option 2 | 3.25_|
| 41761__| 63072_________| 4022____________| option 3 | 5.90_|

I need to get the order_product and all of its options in one overview with a select query but I getting stuck with multiple options for one product.

This is what I have so far

(SELECT
`order_product_id`, 
`order_id`,
`product_id`,
`name`,
`model`,
`quantity`,
`price`,
`total`
FROM `order_product`
WHERE `order_id` = 41761)
UNION (SELECT
0 AS order_product_id,
T1.`order_id`,
0 AS product_id,
T1.`name`,
'option' AS model,
T2.`quantity`,
T1.`price`,
SUM(T1.`price` * T2.`quantity`) AS total
FROM `order_product_option` T1
INNER JOIN `order_product` T2 ON T1.`order_product_id` = T2.`order_product_id`
WHERE T1.`order_id` = 41761)

The outcome of this statement:
| order_product_id | order_id | product_id | name___ | model | quantity | price | total__|
| 63072_________| 41761__| 4022_____| Product 1 | P001_| 2_____ | 23.95 | 47.90 |
| 0_____________| 41761__| 0________| Option 1_ | option_| 2_____ | 0.90 | 10.05 |

But I want the outcome to be:
| order_product_id | order_id | product_id | name___ | model | quantity | price | total__|
| 63072_________| 41761__| 4022_____| Product 1 | P001__| 2_____ | 23.95 | 47.90 |
| 0_____________| 41761__| 0________| Option 1_ | option_| 2_____ | 0.90_ | 1.80_ |
| 0_____________| 41761__| 0________| Option 2_ | option_| 2_____ | 3.25_ | 6.50_ |
| 0_____________| 41761__| 0________| Option 3_ | option_| 2_____ | 5.90_ | 11.80 |

It only selects on option and the total price is incorrect.
How can I fix it so it selects all options with the right price?

Upvotes: 1

Views: 372

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

Try this version:

(SELECT `order_product_id`, `order_id`, `product_id`, `name`,
        `model`, `quantity`, `price`, `total`
 FROM `order_product`
 WHERE `order_id` = 41761
)
UNION ALL
(SELECT 0 AS order_product_id, T1.`order_id`, 0 AS product_id, T1.`name`,
        'option' AS model, T2.`quantity`, T1.`price`, T1.`price` * T2.`quantity` AS total
 FROM `order_product_option` T1 INNER JOIN
      `order_product` T2
       ON T1.`order_product_id` = T2.`order_product_id`
 WHERE T1.`order_id` = 41761
)

The problem with your query is that the second subquery has a sum() function. This turns the query into an aggregation query that returns one row. You don't need the aggregation.

By the way, most databases would generate a compiler error, because you have lots of columns in the select that are not in a group by (because you had no group by). MySQL has extended SQL to allow this construct.

Upvotes: 2

Related Questions