Sahitya Sahitya - 6 months ago 17
SQL Question

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

I have a database with 4 tables (schema specified)


  • USERS :
    Userid, Username, Email (userid primary key)

  • USER_PERSONAL :
    Userid, username, email, mobile (userid primary key)

  • USER_HEALTH :
    Userid, Username, email, Problems (userid primary key)

  • SESSIONS :
    Sessionid, userid, session date (userid foreign key,sessionid primary key)



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?

Answer

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.