0

Sorry for the bad title, but I don't know how to describe what I'm trying to do, but hopefully my explanation will make sense.

Anyway, I have 3 tables in my database at the moment; t_courses, t_users and t_enroll. t_enroll is a join table between t_courses and t_users. The relationship looks like this:

database relational view

On this, I'm performing the following query:

SELECT t_courses.*, COUNT(t_enroll.iduser) AS 'enrolled' 
FROM t_courses 
LEFT JOIN t_enroll ON t_enroll.idcourse = t_courses.id 
WHERE t_courses.date >= CURRENT_DATE
GROUP BY t_enroll.idcourse

The idea here is to return all "upcoming" courses (the WHERE t_courses.date >= CURRENT_DATE part), with a count of the number of users enrolled on each of the courses.

This part works fine, however, the part I'm stuck trying to figure out is how I can make this query user specific.

What I mean, is that I would like to be able to find out if the user id specified as part of the query is enrolled on each course, and return it as a boolean (bit?).

Here is my current attempt to do this:

SELECT t_courses.*, COUNT(t_enroll.iduser) AS 'enrolled',
t_enroll.iduser = 50 as 'isUserEnrolled'
FROM t_courses 
LEFT JOIN t_enroll ON t_enroll.idcourse = t_courses.id 
WHERE t_courses.date >= CURRENT_DATE
GROUP BY t_enroll.idcourse

This doesn't work properly, as isUserEnrolled only returns true when the Id I pass happens to be the first user id related to that course in the join table. It feels like I need some kind of foreach loop, but I know that isn't a thing with SQL.

Does anyone have some ideas of what I could do to achieve the result I'm looking for?

Edit - Thanks to Popeye and his answer/comment, I was able to get a working query going, even fixing another issue I didn't realise my query had!

Here is the final SQL I've used:

SELECT t_courses.*, COUNT(t_enroll.iduser) AS 'enrolled',
IFNULL(MAX(CASE WHEN t_enroll.iduser = 55 then true end), 0) as 'isUserEnrolled'
FROM t_courses 
INNER JOIN t_enroll ON t_enroll.idcourse = t_courses.id 
AND t_courses.date >= CURRENT_DATE
GROUP BY t_enroll.idcourse

I've used IFNULL to return false (0) instead of NULL when the MAX does not find the user id in the join table. This post is where I got the idea to use IFNULL (ISNULL for MSSQL).

Hope this helps someone if they stumble across this!

Jake H
  • 155
  • 2
  • 13

1 Answers1

1

Your query itself is not correct. It is actually INNER JOIN as you have used where clause on left joined table's colimn.

You should move where condition into ON clause as follows:

SELECT t_courses.*, COUNT(t_enroll.iduser) AS 'enrolled',
t_enroll.iduser = 50 as 'isUserEnrolled'
FROM t_courses 
LEFT JOIN t_enroll ON t_enroll.idcourse = t_courses.id 
And t_courses.date >= CURRENT_DATE -- used AND instead of WHERE
GROUP BY t_enroll.idcourse
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • I've moved the where clause to be part of the join statement, and changed the Left join to an inner join; the query is still working as it was before like this. However, I'm still having the same issue of `isUserEnrolled` only being true when the Id I pass happens to be the the first user Id joined to that course in the `t_enroll` table - any ideas for this part? – Jake H Dec 20 '20 at 01:20
  • 1
    Use `max(case when t_enroll.iduser = 50 then true end) as 'isUserEnrolled'` – Popeye Dec 20 '20 at 01:23
  • Yep, that works well! Thank you, I'll accept your answer, and make an edit to my post to show the final SQL. – Jake H Dec 20 '20 at 01:30