CODEWITHSUNDEEP
phpmysqlsqlselect
cppit
cppit

Reputation: 4564

mysql having and where clause on the same query

This is the code I have : 

$sqlz = "SELECT t1.user_id, t2.status, t2.email 
         FROM coverages t1 
         LEFT JOIN users t2 ON t1.user_id = t2.user_id 
         GROUP BY t1.user_id  
         HAVING COUNT(t1.user_id) =".$value;

I would like to add this "WHERE users.email IS NOT NULL" When I do add it, it returns a white page / no results. which I know for a fact there are at least 200 results on the db that contain an email and and match that criteria.

this is an example of what I did that did not work:

 $sqlz =    "SELECT t1.user_id, t2.status, t2.email 
             FROM coverages t1 
             LEFT JOIN users t2 ON t1.user_id = t2.user_id
             WHERE users.email IS NOT NULL 
             GROUP BY t1.user_id  
             HAVING COUNT(t1.user_id) =".$value;

Upvotes: 8

Views: 21785

Answers (1)

John Woo
John Woo

Reputation: 263723

I think you need to use t2 (alias) instead of users.

 $sqlz =    "SELECT t1.user_id, t2.status, t2.email 
             FROM coverages t1 
                     LEFT JOIN users t2 ON t1.user_id = t2.user_id
             WHERE t2.email IS NOT NULL 
             GROUP BY t1.user_id  
             HAVING COUNT(t1.user_id) = " .$value;

Upvotes: 21

Related Questions