CODEWITHSUNDEEP
mysqlsql
Rutuparna Panda
Rutuparna Panda

Reputation: 39

How to use WHERE condition with the LEFT JOIN?

How to implement WHERE conditions in LEFT_JOIN. My query is:

SELECT t1.company_short_name, COUNT(t2.user_ip_address) as chart_accessed 
FROM tblcompanymaster t1 
LEFT JOIN tblhistorymaster t2 ON t1.row_id = t2.company_id 
WHERE t2.user_last_request_time BETWEEN 1603775329 AND 1606280929 
GROUP BY t1.company_short_name

Where I need to found all the company_short_name with the count of access, and if there is no access or the count is 0 then it should come COUNT(t2.user_ip_address) is 0. When I am not using where condition its working perfectly but after the use of where condition its giving only the result where the count is greater then 1. I have tried a lot but I am not able to modify the code. Any suggestions will be of great help

Upvotes: 1

Views: 44

Answers (2)

Fahmi
Fahmi

Reputation: 37473

Put your where clause condition to ON clause

SELECT t1.company_short_name, COUNT(t2.user_ip_address) as chart_accessed 
FROM tblcompanymaster t1 
LEFT JOIN tblhistorymaster t2 ON t1.row_id = t2.company_id 
and t2.user_last_request_time BETWEEN 1603775329 AND 1606280929 
GROUP BY t1.company_short_name

Upvotes: 3

seanb
seanb

Reputation: 6685

You can add the filtering (where) component to the join instead - only taking the values if they exist in t2.

SELECT t1.company_short_name, COUNT(t2.user_ip_address) as chart_accessed 
FROM tblcompanymaster t1 
LEFT JOIN tblhistorymaster t2 ON t1.row_id = t2.company_id AND t2.user_last_request_time BETWEEN 1603775329 AND 1606280929 
GROUP BY t1.company_short_name

Upvotes: 2

Related Questions