CODEWITHSUNDEEP
mysqlsqldatabase
Coderhhz
Coderhhz

Reputation: 340

Sub query in MySQL to retrieve maximum of date if present NULL if absent

I have a database with 4 tables (schema specified)

I want to write a query that retrieves the userid, username,email,mobile,date of last session for every user in the database.

If there is no session entry in the database it says no session or NULL but the other details are printed.

The query I used : 

select users.userid, users.username, users.email, mobile,
    problems, name, date_added, session_date 
from users,sessions,user_personal,user_health 
where sessions.session_date=(select max(session_date) 
                             from sessions 
                             where users.userid=sessions.userid) 
         and sessions.userid=users.userid and users.userid=user_personal.userid 
         and users.userid=user_health.userid;

But this does not retrieve the other details of users without session details. How do I implement the same?

Upvotes: 1

Views: 56

Answers (1)

Pham X. Bach
Pham X. Bach

Reputation: 5442

You could use this

select u.userid, u.username, u.email, up.mobile, uh.problems, s.max_session_date 
from users u
inner join user_personal up on u.userid = up.userid
inner join user_health uh on u.userid = uh.userid
left outer join (select userid, max(session_date) max_session_date
                 from sessions
                 group by userid
                 ) s
    on u.userid = s.userid;

You should not use Old-style-join on your query.

The left outer join let you get NULL (no session) at max_session_date even when user never has a session.

Upvotes: 1

Related Questions