Left Join On table value that is not unique

21 Views Asked by At

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?

0

There are 0 best solutions below