SQL Server 2008 - select based on query results -

i working query

select distinct     se.src_aet "ripped by",      convert(varchar(32), s.created_time, 113) "date/time sent",      p.pat_name "patient name",      p.pat_id "patient id",      s.accession_no "acc #",      p.pat_birthdate "dob",      s.mods_in_study "mod",      convert(varchar(32), s.study_datetime, 113) "study date",      s.study_desc "study desc",      s.study_custom1 "inst name"     dbo.patient p inner join      dbo.study s on s.patient_fk = p.pk inner join      dbo.series se on se.study_fk = s.pk      accession_no '%out%'     , s.created_time >= cast(getdate() date)     , s.created_time < cast(dateadd(day, 1, getdate()) date) order      "date/time sent" desc 

in above example, comes with:

ripped     date/time sent           patient name  patient id   acc#      dob          bc        18 aug 2014 12:58:44:297   doe^john^^^   hosp1234     out     19510101 

i have taken last few columns away (in above example output) simplicity sake here.

it working correctly. comes list of studies have word 'out' in accession_no column , have been written db 'today'.

what wanting return of unique values 'pat_id' in dbo.patient table found in query. of patients found query above might exist in dbo.patient table if have been our site before, have different 'pat_id'.

what want output is:

ripped    date/time sent    patient name     patient id_out      patient id_exist     acc#    dob     study date     study desc     inst name 

the column 'patient id_out' patient id on study has value of 'out' in accession_no column , column 'patient id_exist' existing patient id, if exist. value 'patient id_exist' may blank in cases if patient new.

i thinking needing sub-query here, maybe not right thing in case? correct me if wrong.

edit: have provided example of output. trying figure out if patient in output has different 'patient id' based on searching pat_name , pat_dob in query results. if there or more 1 patient id in pat_id column, them in output.

i hope clear :)

i tackle tasks treating query table , joining it.

select p.* dbo.patient p inner join (your big query) q on q.pat_id = p.pat_id