SQL Server : query for instances of a bit that is 0 for all codes in a table

Question

I have 2 tables:

people

    person_id int
    FirstName varchar
    LastName  varchar

people_codes

    person_id int
    code varchar
    primary bit

If I join the tables I get something like this:

    firstName   lastName   code   primary
    -------------------------------------
    John        Smith      GEN    0
    John        Smith      VAS    1
    Aaron       Johnson    ANE    0
    Allison     Hunt       HOS    0

Ok, so here's the question. How do I query for only the people that have a primary bit of only a 0?

In the above results I only want Aaron and Allison to return, because John Smith has a primary occurrence of 1. Essentially, I can't just say where primary = 0 because I would still get John.

Thank you,

Trout

And what are the desired results? Just `firstName` and `lastName` or `code` too? — Martin Smith, Nov 21 '13 at 17:21

score 1 · Answer 1 · answered Nov 21 '13 at 17:31

1

SELECT FirstName, LastName 
FROM dbo.people AS p
WHERE EXISTS
(
  SELECT 1 FROM dbo.people_codes
    WHERE person_id = p.person_id
    GROUP BY person_id
    HAVING MAX(CONVERT(TINYINT, [primary]) = 0)
);

answered Nov 21 '13 at 17:31

Aaron Bertrand

272,866
37
466
490

score 1 · Answer 2 · edited May 23 '17 at 12:28

1

I recommend using a Not Exists statement, like this:

Select FirstName, LastName
From people
    -- <optional join to other tables>
Where Not Exists
(
    Select 1
    From people_codes
    Where primary = 1
        And people.person_id = people_codes.person_id
)

Why do you want to you use Not Exists? See: NOT IN vs NOT EXISTS

edited May 23 '17 at 12:28

Community

1
1

answered Nov 21 '13 at 17:33

Mike

3,641
3
29
39

Good to know. I will start using `Not Exists` from now on. Thanks – Trout Nov 21 '13 at 18:06

score 0 · Accepted Answer · answered Nov 21 '13 at 17:22

0

select * from people p, people_codes c where p.person_id = c.person_id and p.person_id not in (select person_id from person_codes where primary = 1)

replace * with desired columns, include alias (p.FirstName)

answered Nov 21 '13 at 17:22

Konstantin

3,254
15
20

score 0 · Answer 4 · answered Nov 21 '13 at 17:24

try this

Select distint firstName, lastName  , code   ,primary
From people 
    Inner Join people_codes on people.person_id = people_codes.person_id 
Where people.person_id NOT IN (select person_id 
                               from people_codes 
                               where primary=1)

SQL Server : query for instances of a bit that is 0 for all codes in a table

4 Answers4