Please offer a helping hand.
I have an item table with the following
item_id(pk), name, price, color
I then have a table for sizes
item_id(fk), size, count, sequence
I am trying to allow users to search for an item a LEFT JOIN statement
SELECT table.*, sizes.* FROM item LEFT JOIN sizes ON size.item_id = item.item_id
The issue is I need the results to not duplicate for items with multiple sizes and I also was wondering how to only display items with size counts greater than 0
Hope someone can help
You will need to use some aggregation and group by
first only select the fields you need in the output ( for example you don't want size so don't include it )
you want a sum of the 'count' field - so use the sum function
you need to group by the fields you are not summarising
and you can exclude the zero rows by using a where clause (assuming there are no negative counts) or a having clause
SELECT item_id, item.name, item.price, item.color, sum(size.count)
FROM item LEFT JOIN sizes
where sizes.count > 0
ON sizes.item_id = item.item_id
group by item_id, item.name, item.price, item.color