SQLTeam.com | Weblogs | Forums

How to show only certain records based on values in other records?

sql2008

#1

Hi, I have user records all in a single table and each user can have many records. I only want my query results to show user records for any user ID if NONE of the records for user A have a date field with a date >= 2017-01-01.

So if say there are multiple records for user A in my table:

RID USER DATE
1 A 2015-01-01
2 A 2016-01-01
3 A 2017-01-01

Since there is a record with a date >= 2017-01-01, then I do NOT want to see ANY of the records for user A above.

In the case below, I want to see ALL THREE records for user A in my query results, because NONE of the records have a date in them >=2017-01-01.

RID USER DATE
1 A 2015-01-01
2 A 2016-01-01
3 A 2014-01-01

I tried a NOT EXIST clause but its not working. It only eliminates the records with the date >= 2017-01-01.

How to do this tricky thing in SQL?

Thanks much,

MP


#2

please always provide schema and data to test with?

create table #users(rid int, username varchar(50), date datetime)
insert into #users
select 1, 'A' , '2015-01-01' union 
select 2, 'A' ,'2016-01-01' union
select 3, 'A', '2017-01-01' union
select 4, 'B' , '2015-01-01' union 
select 5, 'B' ,'2016-01-01' union
select 6, 'B', '2014-01-01' 

;with cteExclude
as
(
	select * 
	  from #users
	  where date >= '2017-01-01'

)
select *
from #users u where not exists (select 1 from cteExclude t where t.username = u.username)


drop table #users

#3

Here's another option...

SELECT 
	u.rid, 
	u.username, 
	u.date
FROM
	#users u
WHERE 
	NOT EXISTS (
				SELECT 1
				FROM #users u2
				WHERE 
					u.username = u2.username
					AND u2.date >= '2017-01-01'
				);

#4

Bang! Thanks guys, this worked! I used Jason's actually, but I certainly appreciate the help very much!

I see you did the #, this is a temp table denotation? I did this but just used the plain table names and it seems to work.


#5

The #TableName is a temporary table as you say, and yes changing that for your actual table is probably all that is needed.

Folk here like to test the solutions they offer and it would be appreciated if you provide sample data with any question you ask, and the expected-outcome results data too ... otherwise multiple people here run the risk that they are all winding up doing that at the same time. You'll get more answers too ... unless I happen to have time I am unlikely to bother to answer a question where I first have to spend 5 minutes creating test data AND then write a solution


#6

Ok thanks, will do.