Forum Moderators: open
The scenario: products may have options, and these options usually will be the same price, as in color, and the price in options will be zero. But sometimes the price varies with option, in which case the price in products is zero.
Products table:
record_id ¦ product_id ¦ title ¦ price
111 ¦ 222 ¦ Widgets ¦ 25.00
112 ¦ 223 ¦ Variable Widgets ¦ 0.00
Options table:
record_id ¦ product_id ¦ option_name ¦option_value ¦ price
777 ¦ 222 ¦ Color ¦ Green ¦ 0.00
778 ¦ 222 ¦ Color ¦ Blue ¦ 0.00
779 ¦ 222 ¦ Color ¦ Red ¦ 0.00
780 ¦ 223 ¦ Size ¦ Small ¦ 12.00
781 ¦ 223 ¦ Size ¦ Medium ¦ 15.00
782 ¦ 223 ¦ Size ¦ Large ¦ 20.00
I am looking for a query that displays one row for each price. If it's a same-price option, it should use the products price value and display one row; if it's a variable price, it should display THREE rows:
record_id ¦ product_id ¦ title ¦ price
111 ¦ 222 ¦ Widgets ¦ 25.00
112 ¦ 223 ¦ Variable Widgets ¦ 12.00
112 ¦ 223 ¦ Variable Widgets ¦ 15.00
112 ¦ 223 ¦ Variable Widgets ¦ 20.00
Experimenting with distinct, if(), and joins, and can get one or the other but not both in one query. I know this is a relatively simple task but it's eluding me. :-)
"Forest from the trees," I was making it more complex than it had to be. I forgot that field value restrictions don't always go in the where, you can extend the left join like so . . . .
select
products.record_id,
products.title,
products.price,
options.option_name,
options.option_value,
options.price
from products left join options on
products.product_id=options.product_id
and products.price=0
products.price=0 only limits the left join. DOH! :-)
