Need to qualify the results of a query

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

General SQL Server Forums

New to SQL Server Programming

Need to qualify the results of a query

Author

Topic

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-11-24 : 13:02:48

I have the following query that returns employee numbers:

select distinct personnum from VP_TMSHTCOMMENTV42 where COMMENTTEXT = 'Late'

I have another query that returns another set of employee numbers:
select distinct a.PERSONNUM from VP_WATPA a
join VP_ALLPERSONV42 b on (b.PERSONNUM = a.PERSONNUM)
where a.POLICYNM = 'Yearly Award' and b.EMPLOYMENTSTATUS = 'Active'
and b.HOMELABORLEVELNM5 in ('100141', '100142', '100143',
'100144', '100145', '100146', '100147')
and b.ISWFCATTENDANCELIC = '1' and b.PAYRULENAME not like '%Salar%' and (WATPASTATUSID = '2') and
startdtm = DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
order by a.PERSONNUM

I need a query that will use the 2nd query as a qualifier for the first. Meaning I only want to return employee #s from my 1st query as long as those employee #s are not returned in the 2nd query.

For example, the first query returns:
EE#
1234
3421
5521

And the 2nd query returns:
1234
3421

My query would need to return 5521 since it does not exist in the 2nd query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-24 : 13:08:22

Try this:


select distinct personnum 
from VP_TMSHTCOMMENTV42 v 
where COMMENTTEXT = 'Late' 
	and not exists 
(
	select * 
	from VP_WATPA a 
	join VP_ALLPERSONV42 b on (b.PERSONNUM = a.PERSONNUM)
	where v.PERSONNUM = a.PERSONNUM 
		and a.POLICYNM = 'Yearly Award' and b.EMPLOYMENTSTATUS = 'Active'
		and b.HOMELABORLEVELNM5 in ('100141', '100142', '100143', '100144', '100145', '100146', '100147')
		and b.ISWFCATTENDANCELIC = '1' and b.PAYRULENAME not like '%Salar%' and (WATPASTATUSID = '2') 
		and startdtm = DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0))
)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-11-24 : 13:24:03

It runs but it doesn't return any results. But I do have employees in the first query that do not exist in the 2nd query.

ajsqlnoob
Starting Member

5 Posts

Posted - 2014-11-24 : 13:41:55

quote:
Originally posted by ajsqlnoob

It runs but it doesn't return any results. But I do have employees in the first query that do not exist in the 2nd query.

Nevermind, got it working! Thank you! Just had to join the two tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources