SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Only return where count>1 and one value is null
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GregDDDD
Posting Yak Master

120 Posts

Posted - 02/27/2014 :  14:40:36  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 02/27/2014 :  15:04:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 02/27/2014 :  16:11:00  Show Profile  Reply with Quote
Thank you very much. So simple and eloquent.

Greg
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 02/27/2014 :  16:11:01  Show Profile  Reply with Quote
Thank you very much. So simple and eloquent.

Greg
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000