SQL query to list all student users, add column to show who were logged in at least once I have a query, which lists those users, which are students: 

SELECT  *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE 
ra.roleid = 5  /* is student */


I have a table with user ids, who were logged in at least once: 

SELECT * FROM mdl_user_lastaccess   


I need to list all student users, and have a new column, which shows if the user was logged in or not. Logged in: value 1, not logged in: value 0.

I came up with this query, but the loggedin column is always 1. The 0 values are missing:

SELECT  *, (SELECT IF ((SELECT count(*) as c FROM mdl_user as u, mdl_user_lastaccess as ula WHERE u.id = ula.userid), 1, 0)) as loggedin
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE 
ra.roleid = 5   


Any idea how to fix it?

WRONG SOLUTION:

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid
WHERE 
ra.roleid = 5

ORDER BY loggedin DESC


Results 455 rows, which contains duplicates.

GOOD SOLUTION:

SELECT DISTINCT u.*, IF(ul.userid IS NULL, 0, 1) AS loggedin, ul.courseid
FROM mdl_user u
JOIN mdl_role_assignments ra ON u.id = ra.userid
LEFT JOIN (
    SELECT ula.userid, ula.courseid
    FROM mdl_user_lastaccess ula
    GROUP BY ula.userid
) ul ON u.id = ul.userid
WHERE ra.roleid = 5
ORDER BY loggedin DESC


I had to use DISTINCT to ignore the duplicates.

FINAL GOOD SOLUTION:

SELECT  u.*, IF(la.userid IS NULL, 0, 1) AS loggedin, la.courseid
  FROM mdl_user                 AS u
  JOIN mdl_role_assignments     AS ra ON ra.userid = u.id
  LEFT JOIN mdl_user_lastaccess AS la ON la.userid = u.id
 WHERE ra.roleid = 5           -- is student
 GROUP BY u.id                 -- to collapse all sessions for certain user
 ORDER BY loggedin DESC
Looks like your trying to see if a record for the userid exists in the mdl_user_lastaccess table, you can achieve that with a left join, try this

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid


Another approach using the exists statement

select u.*,
       1 as loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
 where exists (
    select 1
      from mdl_user_lastaccess ul
     where u.id = ul.userid
 )

union

select u.*,
       0 as loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
 where not exists (
    select 1
      from mdl_user_lastaccess ul
     where u.id = ul.userid
 )