November 3, 2003 at 10:56 am
Good Morning all,
I have a table with the following fields:
Procedure_ID int
Procedure_Profile_ID int
One Procedure_ID may have many Procedure_Profile_ID's.
What is the simplest way to SELECT those Procedure_ID's that do not have a Procedure_Profile_ID = '13'?
THANKS!
JM
November 3, 2003 at 11:24 am
quote:
I have a table with the following fields:Procedure_ID int
Procedure_Profile_ID int
One Procedure_ID may have many Procedure_Profile_ID's.
What is the simplest way to SELECT those Procedure_ID's that do not have a Procedure_Profile_ID = '13'?
I don't know about "simplest", but here's a way to do it:
SELECT DISTINCT a.Procedure_ID
FROM <tablename> a
WHERE NOT EXISTS
(SELECT 1
FROM <tablename> b
WHERE a.Procedure_ID = b.Procedure_ID
AND b.Procedure_Profile_ID = 13)
November 3, 2003 at 1:32 pm
THANKS! Worked perfectly.
Such a simple task ... not so simple to code.
JM
November 3, 2003 at 1:34 pm
quote:
THANKS! Worked perfectly.
You're quite welcome!
November 5, 2003 at 7:25 am
select distinct proc_id
from <table1>
where proc_profile_id <> 13
in the case of performance issue ... its preferrable.
fine.
Venkata Srinivasa Rao
Venkata Srinivasa Rao
November 5, 2003 at 7:37 am
quote:
select distinct proc_idfrom <table1>
where proc_profile_id <> 13
in the case of performance issue ... its preferrable.
It's faster, but I don't think it's what jonathanm wanted (though he can certainly correct me on this if he wants). My understanding is that he wants a list of all the Procedure_ID values for which no pairing of that Procedure_ID with 13 even exists.
Your query will still return any Procedure_ID so long as there exists any pairing of a Procedure_ID with any value that is not 13. I don't think that's what he wants.
November 5, 2003 at 10:00 am
Lee is correct. I am looking for Procedure_ID's that do not have a profile of 13.
Thanks anyway for your suggestion Venkata.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply