Unanswered: Search for missing values in SQL. Advanced Query
KEY ID GROUP
1 1 a
2 1 b
3 2 a
4 2 b
5 3 a
6 3 b
7 4 a
8 5 a
This is my simple table I need a query that will identity the ID's that are missing the group "b" but I don't want ID 1,2,3 to come up because they are part of a and b. I just need to see anything missing only "b" but not if it's part of a and b.
query should reveal answer should be missing the group b
I tried the NULL search but since the records dont exist it cant find a null. Any help on writing a query to identify the missing ID without B but exclude ID that are part of A and B
select * from dbo.Groups as GRP1
join dbo.Vcomputer on GRP1.[id] = dbo.Vcomputer.[ID]
join [dbo].[Buildver]on GRP1.[id] = [dbo].[Buildver].[ID]
where [Group] = 'a' and
not exists (select 1 from dbo.Groups as GRP2
where [Group] = 'b' and
GRP2.Id = GRP1.Id)
THANK YOU THANK YOU THANK YOU THANK YOU!! SO SO SO SO MUCH
I spent 2 days trying to figure this out.. Thanks again!!
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.
KEY is a reserved word, GROUP is a reserved word and ID is what ISO-11179 calls a table property so it cannot be a data element name. Why do you need ID at all? It looks like a fake record number that non-SQL programmers build with IDENTITY, but that cannot be right.
Her is my guess at correct DDL:
CREATE TABLE Foobar
(foo_id INTEGER NOT NULL,
foo_version CHAR(1) NOT NULL
CHECK (foo_version IN ('a', 'b')),
PRIMARY KEY (foo_id, foo_version));