Click here to Skip to main content
15,790,135 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables Broadcast and Contact(foreign key of broadcastlist). I want to show broadcast tables all records and count of broadcastlistid in the contact table.

Now I want only those records from broadcast and contact table whose createdBy is 1 and Isdeleted is 0. I get this output from this Query.

DB Fiddle - SQL Database Playground[^]

Now I want to show the count of only those count who has to subscribe to at least one service in the system which are sms_subscribe, email_subscribe, and whatsapp_subscribe. and if all services are unsubscribe then those records should exclude from the count.

What I have tried:

I tried this way but I am not getting my desired output with this.

DB Fiddle -What I have tried [^]
Updated 20-Jul-22 0:30am

1 solution

When mixing AND and OR in an ON clause or a WHERE clause you need to include brackets e.g.
LEFT JOIN Contact c ON = c.broadcast_Id 
                      and c.Created_by = 1 
                      and c.isdeleted = 0
                          c.sms_sub = 'subscribe'
                          or c.eml_sub = 'subscribe'
                          or c.whtsap_sub = 'subscribe'
Hopefully this article will explain why this is necessarySQL: Using Parentheses with And / OR condition is necessary with where clause to gives expected result - Codepedia[^]
Share this answer
HellySoni 20-Jul-22 6:05am    
Thank you so much, It works for me
CHill60 20-Jul-22 6:12am    
Good news! My pleasure
Maciej Los 21-Jul-22 3:32am    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900