CODEWITHSUNDEEP
sqlpostgresql
Nick
Nick

Reputation: 180

SQL query with complex boolean expression as a column

I have the following tables in my PostgreSQL database:

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

Upvotes: 0

Views: 282

Answers (4)

forpas
forpas

Reputation: 164089

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.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

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;

Upvotes: 1

Popeye
Popeye

Reputation: 35900

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;

Upvotes: 1

IVO GELOV
IVO GELOV

Reputation: 14259

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

Upvotes: 0

Related Questions