I have two tables.
The first table is meal_order which has order_id and bundle_id. Now, bundle_id might be the same for multiple order_id and represents that those orders were at one point in one purchase cart and paid together (orders 3 and 4 have the same bundle 5).
order_id bundle_id
-----------------------
1 0
2 0
3 5
4 5
The second table is messages with message_id and each message refers to either order_id (if its bundle_id is 0) or bundle_id It also has status that represents whether the message was already seen or unseen.
message_id order_id bundle_id status
---------------------------------------------------
1 1 0 1
2 2 0 1
3 0 5 0
4 0 5 0
I have a problem figuring out an SQL statement that will count the number of messages that have status = 0 and belong to the same bundle_id ... in other words, I'm trying to find the way to DISTINCT bundle_id in LEFT JOIN meal_order table, however, this statement gives me 4 as a result instead of 2 :
SELECT COUNT(`message_id`) FROM `message` LEFT JOIN `meal_order` ON
`message`.`order_id` = `meal_order`.`order_id` OR `message`.`bundle_id` =
`meal_order`.`bundle_id` WHERE `message`.`status` = 0 GROUP BY
`meal_order`.`bundle_id`
What am I doing wrong?