SQL query with complex boolean expression as a column

Question

I have the following tables in my PostgreSQL database:

classes: Containing class_id and other fields
athletes: Containing athlete_id and other fields
classes_athletes: This is a many to many relationship between classes and athletes. It contains for each class_id which athlete_id are enrolled. In my case it looks like this:

select * FROM classes_athletes;

          class_id | athlete_id
-------------------+------------
                 2 |          1
                 2 |          2
                 2 |          3
                 2 |          4
                 2 |          5
                 1 |          1
                 1 |          2
                 1 |          3
                 1 |          4

Now what I would like to do is to create an SQL query which I can use to indicate if a class contains or not a certain athlete. Here are some examples based on the data above:

For athlete_id=1 it should return
 class_id | athleteIsEnrolled
----------+------------------
        1 |          1
        2 |          1

For athlete_id=99 it should return
 class_id | athleteIsEnrolled
----------+------------------
        1 |          0
        2 |          0

I Tried the following sql query but it returns a value for each entry of classes_athletes instead of only one for each unique class_id:

SELECT c.class_id, CAST(CASE WHEN athlete_id = 1 THEN 1 ELSE 0 END AS bit) as athleteEnrolled
FROM classes_athletes  as c;

And the result is:

          class_id | athleteEnrolled
-------------------+-----------------
                 2 | 1
                 2 | 0
                 2 | 0
                 2 | 0
                 2 | 0
                 1 | 1
                 1 | 0
                 1 | 0
                 1 | 0

Have you tried just adding a distinct to your last select statement? — Verma
– Verma, Commented Dec 30, 2020 at 7:59

Popeye · Accepted Answer · 2020-12-30 08:32:36Z

1

Use the conditional aggregation as follows:

SELECT c.class_id, Count(1) as athleteEnrolled
FROM classes c 
left join classes_athletes ca 
  on c.class_id = ca.class_id
 and ca.athlete_id = 1
Group by c.class_id;

edited Dec 30, 2020 at 8:32

answered Dec 30, 2020 at 8:02

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Popeye Over a year ago

Thanks @TimBiegeleisen for noticing. Answer is updated!!

Tim Biegeleisen · Accepted Answer · 2020-12-30 08:38:12Z

1

Using a left join approach:

SELECT
    c.class_id,
    COUNT(ca.class_id) AS athleteEnrolled
FROM classes c
LEFT JOIN classes_athletes ca
    ON ca.class_id = c.class_id AND
       ca.athlete_id = 1
GROUP BY
    c.class_id
ORDER BY
    c.class_id;

edited Dec 30, 2020 at 8:38

answered Dec 30, 2020 at 8:01

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

IVO GELOV · Accepted Answer · 2020-12-30 08:02:51Z

0

Using a subquery approach:

SELECT 
  class_id,
  (SELECT COUNT(1) FROM classes_athletes AS enrollment 
    WHERE enrollment.class_id = classes.class_id AND athlete_id = 99
  ) AS athleteEnrolled
FROM classes
ORDER BY class_id

answered Dec 30, 2020 at 8:02

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

Comments

forpas · Accepted Answer · 2020-12-30 08:51:21Z

0

You can do this with EXISTS:

SELECT c.class_id,
  EXISTS (
    SELECT 1 FROM classes_athletes ca 
    WHERE ca.class_id = c.class_id AND athlete_id = ?
  ) athleteEnrolled
FROM classes c

Replace ? with the athlete_id you search for.
This query returns a boolean result true or false.
If you want the result as an integer 1 or 0 you can cast it to integer with ::int

See the demo.

answered Dec 30, 2020 at 8:51

forpas

165k10 gold badges51 silver badges85 bronze badges

Collectives™ on Stack Overflow

SQL query with complex boolean expression as a column

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related