Jump to content
glendango

selecting users with null entries

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']."'   ") ;
Edited by glendango

Share this post


Link to post
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.

Share this post


Link to post
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 

Edited by glendango

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.