Only return where count>1 and one value is null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Only return where count>1 and one value is null

Author

Topic

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-02-27 : 14:40:36

The code and query below will create a table and return two rows because only Jane Doe and John Doe have multiple rows. Sam Spade and Buck Naked each only have one row.

What I would like it do is only return the row for John Doe because he has a NULL for category_name. Jane has multiple rows but all of her values in category_name are populated, so I don't care about her.

The query should look for people with multiple rows AND one or more of the category rows is NULL. Is this possible?

Greg

declare @tbl_people TABLE (
[person_id] [int] NOT NULL,
[category_name] varchar(30) NULL,
[person_name] varchar(30) NOT NULL
)

insert into @tbl_people
values
(1001, 'category_1', 'John Doe')

insert into @tbl_people
values
(1001, 'category_1', 'John Doe')

insert into @tbl_people
values
(1001, NULL, 'John Doe')

insert into @tbl_people
values
(1002, 'category_1', 'Jane Doe')

insert into @tbl_people
values
(1002, 'category_1', 'Jane Doe')

insert into @tbl_people
values
(1002, 'category_1', 'Jane Doe')

insert into @tbl_people
values
(1003, 'category_3', 'Sam Spade')

insert into @tbl_people
values
(1004, 'category_2', 'Buck Naked')

select count(person_id), [person_name] from @tbl_people
group by [person_name]
having count(person_id) >1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-27 : 15:04:52

The answer is Relational Division

SELECT		[Person_Name]
FROM		@tbl_People
GROUP BY	[Person_Name]
HAVING		MAX(CASE WHEN [Category_Name] IS NULL THEN 1 ELSE 0 END) = 1
		AND MIN(CASE WHEN [Category_Name] IS NULL THEN 1 ELSE 0 END) = 0;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-02-27 : 16:11:00

Thank you very much. So simple and eloquent.

Greg

GregDDDD
Posting Yak Master

120 Posts

Posted - 2014-02-27 : 16:11:01

Thank you very much. So simple and eloquent.

Greg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources