Hey folks, I’m working on a database project and could use some help. I’ve got three tables: learners
, registrations
, and classes
. Each learner can sign up for any class, and classes are ranked from 1 (basic) to 4 (expert).
I’m trying to pull learner info to see if they’re signed up for classes at each level. I want my output to look something like this:
learner_id | learner_name | basic | intermediate | advanced | expert
----------------------------------------------------------------
98765 John Doe Yes Yes No No
87654 Jane Smith Yes Yes Yes No
Here’s what my tables look like:
learners
table:
learner_id | full_name | contact | school_code | year
classes
table:
class_id | title | summary | rank | points | start_date | end_date
registrations
table:
reg_id | learner_id | class_id | signup_date
Any ideas on how to write a query for this? Thanks in advance!
hey mate, i think i got a solution for ya. try this:
SELECT l.learner_id, l.full_name,
IF(SUM(c.rank=1)>0,‘Yes’,‘No’) basic,
IF(SUM(c.rank=2)>0,‘Yes’,‘No’) intermediate,
IF(SUM(c.rank=3)>0,‘Yes’,‘No’) advanced,
IF(SUM(c.rank=4)>0,‘Yes’,‘No’) expert
FROM learners l
LEFT JOIN registrations r ON l.learner_id=r.learner_id
LEFT JOIN classes c ON r.class_id=c.class_id
GROUP BY l.learner_id, l.full_name
this should do the trick! lmk if u need more help
To tackle this, you’ll need to use a combination of JOIN operations and conditional aggregation. Here’s a query that should do the trick:
SELECT
l.learner_id,
l.full_name AS learner_name,
MAX(CASE WHEN c.rank = 1 THEN 'Yes' ELSE 'No' END) AS basic,
MAX(CASE WHEN c.rank = 2 THEN 'Yes' ELSE 'No' END) AS intermediate,
MAX(CASE WHEN c.rank = 3 THEN 'Yes' ELSE 'No' END) AS advanced,
MAX(CASE WHEN c.rank = 4 THEN 'Yes' ELSE 'No' END) AS expert
FROM
learners l
LEFT JOIN
registrations r ON l.learner_id = r.learner_id
LEFT JOIN
classes c ON r.class_id = c.class_id
GROUP BY
l.learner_id, l.full_name;
This query uses LEFT JOINs to include all learners, even if they haven’t registered for any classes. The CASE statements inside MAX functions create the Yes/No columns for each rank level. Hope this helps with your project!