Jump to content

selecting users with null entries


glendango

Recommended Posts

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']."'   ") ;
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.