selecting users with null entries

[glendango]

i have 2 queries . the first query gives me all the users with the same company_id and displays a '0' against their name if they have no enties. but the second query doesn't display the users with no entries against their name. in this case the query counts dates of entries they have made. 

 

I am guessing its something to do with using 'between' and 'where'. 

 

 

any ideas ? thx

 

$result = mysqli_query($conn,"SELECT u.name , u.surname , u.id , u.company_id, count(f.date_made) as date_made FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id  AND DATE(f.date_made) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE()   group by  u.id having u.company_id='".$_SESSION['company_id']."'    ") ;

 

//  company session from this month,,above code gives all users even if 0,,below desnt??
$result = mysqli_query($conn,"SELECT u.id , u.name , u.surname ,  u.company_id, count(f.date_made) as date_made FROM users u LEFT JOIN firsts f ON u.id  = f.usr_id AND DATE(f.date_made) WHERE year(curdate()) = year(date_made) and month(curdate()) = month(date_made)   group by  u.id having u.company_id='".$_SESSION['company_id']."'   ") ;

Edited November 1, 2017 by glendango

[Barand]

I suggest you refer back your previous topic. I have already told that selection criteria on the LEFT JOINed table (firsts) need to go in the JOIN ON conditions and NOT in the WHERE clause. I also told you to use WHERE, and not HAVING, for the users selection in this situation.

 

Read, learn and remember.

 

SELECT u.id 
, u.name 
, u.surname 
, u.company_id
, count(f.date_made) as date_made 
FROM users u 
  LEFT JOIN 
  firsts f 
    ON u.id  = f.usr_id 
    AND year(curdate()) = year(date_made) and month(curdate()) = month(date_made)   
WHERE u.company_id = ?
GROUP BY  u.id "

Use prepared queries.

[glendango]

i changed the where to 'and'  and it worked...   thanks for your advice... i keep getting errors when i try your code with my session company_id....     i know your code is correct but this is what i ended up with to make my code work...

 

your advice still helped me plaster the query together though as the logic is certain things like where, and etc only work together in certain ways . thx again 

Edited November 1, 2017 by glendango