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
 how does this sql work?

Author  Topic 

learnsql123
Starting Member

23 Posts

Posted - 2013-02-18 : 11:48:31
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

52326 Posts

Posted - 2013-02-18 : 12:01:20
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

23 Posts

Posted - 2013-02-18 : 22:01:58
Thanks for explaining. Now it makes sense.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 23:38:02
welcome

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

Go to Top of Page
   

- Advertisement -