NEW TO SQL - SQL Server Forums

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

NEW TO SQL

Author

Topic

H
Starting Member

23 Posts

Posted - 2011-10-24 : 16:00:54

I am new to SQL
I need help with one query
I have 3 tables – 1. Patient_Master 2. Patient_checkin 3. Prescription
Patient_master fields/columns .. id, lastname, firstname, middlename ….
Patient checkin fields/columns patient_id, checkin_date, …..
Presription fields/columns .. patient_id, tran_date, drug, start_date

Patient has NEVER been checked in/seen in the office – therefore has NOT been seen by the provider during 2010 nor 2011. However, at sometime during 2010 and/or 2011 medication was prescribed. I need list of patient that were never seen by provider / doctor but prescription has been assign

select distinct patient_master.id, patient_master.lastname, patient_master.firstname from patient_master, patient_checkin where (patient_master.id<>patient_checkin.patient_id )
and patient_checkin.checkin_date <='2011-10-23'

above query gives me all the patient who did not visited until 2011-10-23
BUT now I need to get list of patient who were prescribe (from this list)

Can any one help please

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-10-25 : 00:12:09

Read about Not Exists in BOL and show us what have you tried.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-25 : 00:17:08

sounds like below


select pm.*
from patient_master pm
left join Patient_checkin pc
on oc.patient_Id = pm.id
and pc.checkin_date <='2011-10-23' 
inner join Prescription p
on p.patient_id = pm.id
where pc.id is null

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

Sachin.Nand

2937 Posts

Posted - 2011-10-25 : 07:04:34

quote:
Originally posted by visakh16

sounds like below

select pm.*
from patient_master pm
left join Patient_checkin pc
on oc.patient_Id = pm.id
and pc.checkin_date <='2011-10-23'
inner join Prescription p
on p.patient_id = pm.id
where pc.id is null

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

An Exists in this condition will outperform a Join.Exists will use a semi join while a join will go for a hash match in case cardinality is very low(in combination of 2 tables) as an Exists will cause the matching name to be returned once for each row in the outer table if it satisfies the join condition.But when the cardinality is high both the queries will almost perform the same but an Exists will take advantage of the clustered index on the joining column of the exists clause.

OP just wants to check the existence of the patient name who were prescribed but with a join the execution will be done for each row that exists in the outer table and a result will be returned for each row in the outer table.

Also I am sure the OP will use a distinct clause to get distinct patient names if he uses a join as a join will return all rows for the columns in the outer table that are not a part of the join condition and distinct will have to eliminate the duplicates to return distinct values which might or might not include a sort operator.

PBUH

H
Starting Member

23 Posts

Posted - 2011-10-25 : 10:01:23

Thank you all for your input
Thanks Visakh and Sachin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-26 : 00:50:32

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources