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
 General SQL Server Forums
 New to SQL Server Programming
 how does this sql work?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learnsql123
Starting Member

USA
23 Posts

Posted - 02/18/2013 :  11:48:31  Show Profile  Reply with Quote
I have a sql that uses not exist, but I am doubtful on what column it is trying to match the Not Exist. Any help is highly appreciated, Thank You in advance.


select 
		C.clinic_id,
		P.provider_id,
		P.eff_date,
		'PROV'
		--,@etl_job_id
		--,@run_date
	from provider P
	join clinic C on P.primary_ein = C.primary_ein
		               and C.source_type = 'PROV'
		               and (C.exp_date is null or C.exp_date >= GETDATE())
	where P.cycle_id = 854 --1012
	and P.provider_id is not null
	and NOT exists
	(select 1 
		FROM health H
		where P.provider_id = H.provider_id
			and C.clinic_id = H.clinic_id
			and H.staff_id is null)


The first column in health H table is an identity column 'Entity_id'.
Is the above query trying to see C.clinic_id that does on exist in H.EntityID list in the sub query?




visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/18/2013 :  12:01:20  Show Profile  Reply with Quote
its looking for both the conditions

ie P.provider_id = H.provider_id
and C.clinic_id = H.clinic_id

and returning records which doesnt have any of these conditions existing

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

learnsql123
Starting Member

USA
23 Posts

Posted - 02/18/2013 :  22:01:58  Show Profile  Reply with Quote
Thanks for explaining. Now it makes sense.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/18/2013 :  23:38:02  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.06 seconds. Powered By: Snitz Forums 2000