SELECT help

9 Feb 2006 5:58 PM

SJ

I have to select 2 columns from a table where the 2 column combination does
not exist in another table.
I have table #temp1
(patient_id         INT             NULL,
age        INT       NULL,
svc_date                  DATETIME   NULL,
hospital_id               CHAR(9)      NULL,
prov_name              CHAR(50)    NULL,
service_units            INT            NULL)

and table #temp2
(patient_id         INT             NULL,
age        INT       NULL,
svc_date                  DATETIME   NULL,
hospital_id               CHAR(9)      NULL,
prov_name              CHAR(50)    NULL)

I want to be able to select patient_id, hospital_id from #temp1
where these 2 column combination does not exist in #temp2 - I tried
SELECT distinct patient_id, hospital_id
from #temp1
where not exists (SELECT patient_id, hospital_id
                          from #temp2)
did not work
I am sure there is an efficient way of doing this other than converting
patient_id into a char field like in the following query ..

select distinct patient_id, hospital_id
from #temp1
where convert(char(7), patient_id)+hospital_id not in
           (select convert(char(7), patient_id)+hospital_id from #temp2)

9 Feb 2006 6:23 PM

Jim Underwood

Your exists is not working because you are checking to see if any row exists
in table2, when you want to see if a matching row exists. To check for a
matching row, you need to set the columns of your inner query equal to the
columns of the outer query.

Try this...

SELECT distinct t1.patient_id, t1.hospital_id
from #temp1 t1
where not exists (SELECT 1 from #temp2 t2
where t1.patient_id = t2.patient_id
and t1.hospital_id=t2.hospital_id)

Show quoteHide quote

"SJ" <S*@discussions.microsoft.com> wrote in message
news:77410903-8C21-4DBD-8A12-2F2686D9026B@microsoft.com...
>   I have to select 2 columns from a table where the 2 column combination
does
> not exist in another table.
> I have table #temp1
> (patient_id     INT             NULL,
>   age INT    NULL,
>   svc_date                  DATETIME   NULL,
>   hospital_id               CHAR(9)      NULL,
>   prov_name              CHAR(50)    NULL,
>   service_units            INT            NULL)
>
> and table #temp2
> (patient_id     INT             NULL,
>   age INT    NULL,
>   svc_date                  DATETIME   NULL,
>   hospital_id               CHAR(9)      NULL,
>   prov_name              CHAR(50)    NULL)
>
> I want to be able to select patient_id, hospital_id from #temp1
> where these 2 column combination does not exist in #temp2 - I tried
> SELECT distinct patient_id, hospital_id
> from #temp1
> where not exists (SELECT patient_id, hospital_id
>                           from #temp2)
> did not work
> I am sure there is an efficient way of doing this other than converting
> patient_id into a char field like in the following query ..
>
> select distinct patient_id, hospital_id
> from #temp1
> where convert(char(7), patient_id)+hospital_id not in
>            (select convert(char(7), patient_id)+hospital_id from #temp2)