CODEWITHSUNDEEP
sql-server
Charkins12
Charkins12

Reputation: 190

SQL Find Groups that do not have Value

I'm trying to find some data grouped by ids like so

Create Table FormFields (Label varchar(100), FormId int)
Insert Into FormFields Values ('First Name', 1)
Insert Into FormFields Values ('Last Name', 1)
Insert Into FormFields Values ('Email', 1)

Insert Into FormFields Values ('First Name', 2)
Insert Into FormFields Values ('Last Name', 2)
Insert Into FormFields Values ('Email', 2)
Insert Into FormFields Values ('Phone', 2)

Insert Into FormFields Values ('Email', 3)
Insert Into FormFields Values ('Phone', 3)
Insert Into FormFields Values ('Birthday', 3)

But I'm trying to find all instances where the "group" (the data with the same id) does not have an entry for "First Name" and "Last Name"

I've tried:

select distinct Label, FormId 
from FormFields
where Label not in
(
    select distinct Label
    from FormFields
    where Label= 'First Name' or Label= 'Last Name'
)
order by FormId

But that only gives the other fields without a reference to see if there is actually a first and last name.

My Desired output would just show Formfields where ID = 3 since that group (of items listed with id 3) do not have an entry for "First Name" and "Last Name"

Thanks for the help here, very confused if this is even possible.

Upvotes: 1

Views: 54

Answers (2)

Rasanjana N
Rasanjana N

Reputation: 1400

What you have tries is very close to your solution. Try change what you've tried like below.

SELECT DISTINCT Label, FormId 
FROM FormFields
WHERE FormId NOT IN
(
    SELECT DISTINCT FormId
    FROM FormFields
    WHERE Label= 'First Name' OR Label= 'Last Name'
)
ORDER By FormId

OR another solution :

SELECT * FROM FormFields 
WHERE FormId NOT IN 
(
    SELECT DISTINCT FormId 
    FROM FormFields 
    WHERE Label IN ('First Name' , 'Last Name')
)

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Thanks for providing data and a clearer explanation. There are several ways to accomplish this. Here is one of them.

select *
from FormFields
where FormId
not in
(
    select FormId
    from FormFields
    where Label in ('First Name', 'Last Name')
)

Upvotes: 1

Related Questions