Query help

• ygrno93

  SSC Rookie

  Points: 36

  More actions

  November 29, 2017 at 10:12 am

  #326525

  Lets say I have a database of an hospital.
  so..  i got a table with 2 columns: patients_ID ,treatment_ID and for the record lets say we have another third primary key column , doesn't matter what exactly.
  now, I want to extract from the table all the patients_ID who got at least (or more) all the treatment's that patient with id of '1' have.

  for example:

  patients_ID                                      treatment_ID
         1                                                         111
         1                                                         222
         1                                                         333
         2                                                         111
         2                                                         333
         5                                                         111
         5                                                         222
         5                                                         333
         5                                                         444

  ass you can  see, from the table above I would want my query to return the table:

  patients_ID 
           5
  cause only him got the same treatment as patient id=1 , and i don't care that he also got treatment 444 in addition to that. and patient_id=2 doesn't belong cause he's missing treatment "222"
  Thank you, in advance

• xDJR1875

  Ten Centuries

  Points: 1100

  More actions

  November 29, 2017 at 10:52 am

  #1969948

  Kind of an odd query, but this might help get you closer.

  DECLARE @PatientIDToMatch int = 1
SELECT a.Patients_ID, Count(*)
FROM dbo.PatientTreatment a
WHERE a.Treatement_ID IN (Select Treatement_ID
                             from dbo.PatientTreatment b
                            where Patients_ID = @PatientIDToMatch
                             AND a.Patients_ID <> b.Patients_ID
                         )
GROUP BY Patients_ID
HAVING COUNT(*) = (SELECT Count(*) from dbo.PatientTreatment where Patients_ID = @PatientIDToMatch)

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 30, 2017 at 4:59 am

  #1970015

  See if it makes sense:

  create table #PatientTreatment (
patientID int,
TreatmentID int
)

  insert into #PatientTreatment
select 1, 111
union
select 1, 222
union
select 1, 333
union
select 2, 111
union
select 2, 333
union
select 5, 111
union
select 5, 222
union
select 5, 333
union
select 5, 444
go

  declare @PatientToMatch int
set @PatientToMatch = 1

  select O.patientID
from
    (select patientID
    from #PatientTreatment
    where patientID <> @PatientToMatch
    group by patientID
    ) O -- List of all "other" patients
    inner join #PatientTreatment BT ON BT.patientID = @PatientToMatch -- applying the list of all treatments for "base" patient
    left join #PatientTreatment M on M.TreatmentID = BT.TreatmentID and M.patientID = O.patientID
            -- matching "base" patient treatments to "other" patient treatments
group by O.patientID
having count(BT.treatmentID) = COUNT(M.TreatmentID) -- excluding "other" patients which have a missing treatment comparing to the "base" one

  drop table #PatientTreatment


  _____________
  Code for TallyGenerator

• ygrno93

  SSC Rookie

  Points: 36

  More actions

  November 30, 2017 at 6:22 am

  #1970023

  Thank you guys, it was very helpful

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 30, 2017 at 9:23 am

  #1970069

  Sergiy - Thursday, November 30, 2017 4:59 AM

  See if it makes sense:

  create table #PatientTreatment (
patientID int,
TreatmentID int
)

  insert into #PatientTreatment
select 1, 111
union
select 1, 222
union
select 1, 333
union
select 2, 111
union
select 2, 333
union
select 5, 111
union
select 5, 222
union
select 5, 333
union
select 5, 444
go

  declare @PatientToMatch int
set @PatientToMatch = 1

  select O.patientID
from
    (select patientID
    from #PatientTreatment
    where patientID <> @PatientToMatch
    group by patientID
    ) O -- List of all "other" patients
    inner join #PatientTreatment BT ON BT.patientID = @PatientToMatch -- applying the list of all treatments for "base" patient
    left join #PatientTreatment M on M.TreatmentID = BT.TreatmentID and M.patientID = O.patientID
            -- matching "base" patient treatments to "other" patient treatments
group by O.patientID
having count(BT.treatmentID) = COUNT(M.TreatmentID) -- excluding "other" patients which have a missing treatment comparing to the "base" one

  drop table #PatientTreatment


  This will miss those patients who've had the same treatments but also had other treatments.   Change the = in the HAVING clause to <= and you'll meet the actual requirement.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 30, 2017 at 1:17 pm

  #1970164

  No, it won't miss them.

  Because of LEFT join all the treatments which are not in the base set will be simply omitted.

  _____________
  Code for TallyGenerator

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 30, 2017 at 2:12 pm

  #1970176

  Just a different option, for the fun:
  
DECLARE @Sample TABLE(
  patients_ID int,
  treatment_ID int
);
INSERT INTO @Sample
VALUES
(1, 111),
(1, 222),
(1, 333),
(2, 111),
(2, 333),
(5, 111),
(5, 222),
(5, 333),
(5, 444);

  DECLARE @Patient int = 1;
SELECT *
FROM @Sample s
WHERE s.patients_ID <> @Patient
AND NOT EXISTS(
  SELECT treatment_ID FROM @Sample i WHERE i.patients_ID = @Patient
  EXCEPT
  SELECT treatment_ID FROM @Sample x WHERE x.patients_ID = s.patients_ID
  )
 


  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  November 30, 2017 at 2:32 pm

  #1970183

  Sergiy - Thursday, November 30, 2017 1:17 PM

  No, it won't miss them.Because of LEFT join all the treatments which are not in the base set will be simply omitted.

  My bad...   was thinking it was going to count all the other treatments not in the base set, and that's not correct.   Brain is not working so well today.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 30, 2017 at 2:50 pm

  #1970185

  sgmunson - Thursday, November 30, 2017 2:32 PM

   Brain is not working so well today.

  At least you have one...
  🙂

  _____________
  Code for TallyGenerator

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 30, 2017 at 3:04 pm

  #1970186

  Luis Cazares - Thursday, November 30, 2017 2:12 PM

  Just a different option, for the fun:
  
  DECLARE @Sample TABLE(
    patients_ID int,
    treatment_ID int
  );
  INSERT INTO @Sample
  VALUES
  (1, 111),
  (1, 222),
  (1, 333),
  (2, 111),
  (2, 333),
  (5, 111),
  (5, 222),
  (5, 333),
  (5, 444);

  DECLARE @Patient int = 1;
SELECT *
FROM @Sample s
WHERE s.patients_ID <> @Patient
AND NOT EXISTS(
  SELECT treatment_ID FROM @Sample i WHERE i.patients_ID = @Patient
  EXCEPT
  SELECT treatment_ID FROM @Sample x WHERE x.patients_ID = s.patients_ID
  )
 


  Check this out:

  DECLARE @Patient int = 1;
set nocount on
set statistics io, time on
-- Original Luis's version:
SELECT *
FROM @Sample s
WHERE s.patients_ID <> @Patient
AND NOT EXISTS(
SELECT treatment_ID FROM @Sample i WHERE i.patients_ID = @Patient
EXCEPT
SELECT treatment_ID FROM @Sample x WHERE x.patients_ID = s.patients_ID
)

  -- Slightly modified version, replacing "advanced querying feature" (EXCEPT) with an "old school" one (WHERE NOT EXISTS):
SELECT *
FROM @Sample s
WHERE s.patients_ID <> @Patient
AND NOT EXISTS(
SELECT * FROM @Sample i WHERE i.patients_ID = @Patient
and not exists (
    SELECT * FROM @Sample x
    WHERE x.patients_ID = s.patients_ID
        AND x.treatment_ID = i.treatment_ID
        )
)
set statistics io, time off

  Table '#0FE30493'. Scan count 9, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 474 ms.
Table '#0FE30493'. Scan count 3, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  SQL Server Execution Times:
 CPU time = 0 ms, elapsed time = 258 ms.


  EXCEPT vs. NOT EXISTS is like DISTINCT vs. GROUP BY
  

  _____________
  Code for TallyGenerator