Left join with multiple values in where clause

Question

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.

Here is the Fiddle link : Fiddle

[mre] [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [ask] [Help] — philipxy, Jul 15 '22 at 19:20
Duplicate of [Left Join With Where Clause](https://stackoverflow.com/q/4752455/3404097). PS This is a faq. But one must pin down via a [mre] & write many clear, concise & precise phrasings of one's question/problem/goal to search. PS Please format code reasonably. — philipxy, Jul 15 '22 at 19:22

Paulo Fernando · Accepted Answer · 2022-07-15T13:13:41.240

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

Left join with multiple values in where clause

1 Answers1