CODEWITHSUNDEEP
sql-servert-sql
James Hay
James Hay

Reputation: 7315

Select column based on whether a specific row in another table exists

Question is similar to this one How to write a MySQL query that returns a temporary column containing flags for whether or not an item related to that row exists in another table

Except that I need to be more specific about which rows exists

I have two tables: 'competitions' and 'competition_entries'

Competitions:

ID    | NAME      | TYPE
--------------------------------
1     | Example   | example type
2     | Another   | example type

Competition Entries

ID    | USERID    | COMPETITIONID
---------------------------------
1     | 100       | 1
2     | 110       | 1
3     | 110       | 2
4     | 120       | 1

I want to select the competitions but add an additional column which specifies whether the user has entered the competition or not. This is my current SELECT statement

SELECT
    c.[ID],
    c.[NAME],
    c.[TYPE],
    (CASE 
        WHEN e.ID IS NOT NULL AND e.USERID = @userid THEN 1 
        ELSE 0 
        END
    ) AS 'ENTERED'      

FROM competitions AS c
LEFT OUTER JOIN competition_entries AS e
ON e.COMPETITIONID = c.ID

My desired result set from setting the @userid parameter to 110 is this

ID    | NAME    | TYPE         | ENTERED
-------------------------------------
1     | Example | example type | 1
2     | Another | example type | 1

But instead I get this

ID    | NAME    | TYPE         | ENTERED
-------------------------------------
1     | Example | example type | 0
1     | Example | example type | 1
1     | Example | example type | 0
2     | Another | example type | 1

Because it's counting the entries for all user ids

Upvotes: 1

Views: 2543

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

Fixing your query

SELECT
    c.[ID],
    c.[NAME],
    c.[TYPE],
    MAX(CASE 
        WHEN e.ID IS NOT NULL AND e.USERID = @userid THEN 1 
        ELSE 0 
        END
    ) AS 'ENTERED'      
FROM competitions AS c
LEFT OUTER JOIN competition_entries AS e ON e.COMPETITIONID = c.ID
GROUP BY
    c.[ID],
    c.[NAME],
    c.[TYPE]

An alternative is to rewrite it using EXISTS which is pretty much the same but may be easier to understand.
BTW, using single quotes on the column name is deprecated. Use square brackets.

SELECT
    c.[ID],
    c.[NAME],
    c.[TYPE],
    CASE WHEN EXISTS (
        SELECT *
        FROM competition_entries AS e
        WHERE e.COMPETITIONID = c.ID
          AND e.USERID = @userid) THEN 1 ELSE 0 END [ENTERED]
FROM competitions AS c

Upvotes: 3

Related Questions