Left join with multiple values in where clause-CodePudding

I have two tables broadcast and contact and I want the count of the number of recipients in that particular broadcast if there is no user still all broadcast lists should be displayed. for this, I got my desired output with this left join Query.

SELECT b.id, b.brl_name, COUNT(c.broadcast_list_id) AS Recepients FROM broadcastlistsms b LEFT JOIN addcontact c ON b.id = c.broadcast_list_id GROUP BY b.id,b.brl_name;

But I have 2 common fields in both tables that are created_by and isdeleted, isdeleted(1) is that particular contact and broadcast is deleted. So I want to get only that contact count which is not deleted and created by a particular user. and similarly to broadcast, the broadcast should not be deleted and created by the particular user.

I tried this but it did not give broadcast entry which has 0 contacts.

SELECT b.id, b.brl_name, COUNT(c.broadcast_list_id) AS Recepients FROM broadcastlistsms b LEFT JOIN addcontact c ON b.id = c.broadcast_list_id 
WHERE (b.createdby_id = 1 and b.isdeleted = 0) and (c.createdby_id = 1 and c.isdeleted = 0)
GROUP BY b.id,b.brl_name;

In the future will have 3 more fields in the contact table like isdeleted, then also I need to check all of that 3 field and need a count value as per that field.

CodePudding user response：

Next time, please, provide the data in text format, or event better in a SQL Fiddle like this: https://www.db-fiddle.com/f/aYULH8tP5yVB18ffkJNvFe/0

Try to organize your queries so that they can be more readable.

The problem was that you added the Contact conditions in the WHERE, by doing so, Broadcast will also be filtered, you must add all the Contact conditions in the LEFT JOIN.

To get the desired output you showed in the image (listing all broadcasts), you can't filter the broadcast:

SELECT b.id, 
       b.name, 
       COUNT(c.broadcast_Id) AS Recepients 
FROM Broadcast b 
LEFT JOIN Contact c ON b.id = c.broadcast_Id 
                   AND c.Created_by = 1 
                   AND c.isdeleted = 0
GROUP BY b.id,
         b.name

But to get the output you described (showing only what is not deleted and filtered by a specific user) is this:

SELECT b.id, 
       b.name, 
       COUNT(c.broadcast_Id) AS Recepients 
FROM Broadcast b 
LEFT JOIN Contact c ON b.id = c.broadcast_Id 
                   AND c.Created_by = 1 
                   AND c.isdeleted = 0
WHERE b.IsDeleted = 0 
  AND b.Created_by = 1
GROUP BY b.id,
         b.name