CODEWITHSUNDEEP
sql-server
Phil
Phil

Reputation: 55

sql select based off multiple values

We're using SQL Server 2008, a sample of the table below. I need to query the below table and only give me the link result if no code of 'C' exists, but because link 957 below already has a C code, I dont want 957 returned at all, I would just want 950 and 955.

link                      code
---------------------------------
957                         A
957                         B
957                         C
957                         D
955                         A
955                         B
950                         D
950                         E

Upvotes: 0

Views: 67

Answers (3)

Will Pan
Will Pan

Reputation: 282

In case you didn't want to use a nested query, the following will also work:

SELECT     link
FROM       table
GROUP BY   link
HAVING     SUM(CASE WHEN CODE = 'C' THEN 1 ELSE 0 END) = 0

Upvotes: 2

Lóri Nóda
Lóri Nóda

Reputation: 750

Try with the following example:

SELECT DISTINCT link
FROM TABLE
WHERE link NOT IN (
        SELECT link
        FROM TABLE
        WHERE code LIKE 'C'
        )

Upvotes: 1

John Woo
John Woo

Reputation: 263723

You can use NOT EXISTS() on this,

SELECT  a.*
FROM    tableName a
WHERE   NOT EXISTS
        (
            SELECT  1
            FROM    tableName b
            WHERE   a.link = b.link AND
                    b.code = 'c'
        )

But if you just want to get the link alone,

SELECT  DISTINCT a.link
FROM    tableName a
WHERE   NOT EXISTS
        (
            SELECT  1
            FROM    tableName b
            WHERE   a.link = b.link AND
                    b.code = 'c'
        )

Upvotes: 0

Related Questions