October 11, 2007 at 8:10 am
Scenario: We have a table that holds all the information for a form template. The primary key is form_id and there are many other fields that define the form. One important field is the Ident_type_id field. This defines a data entry point. For example Ident_type_id = 2 would indicate in our HTML code that this is a text box field for First Name. We have many forms and they all use ident_type_id = 2 because we collect First Name as just about everyone does.
Table structure (simplified)
Field Name: Form_ID, int (Value is a unique id that denotes which form is being used or requested. 1 = General Information as an example)
Field Name: Ident_Type_ID, int (value may or may not be used on more than 1 form. 2 = First Name as an example and it is on 5 of our forms)
So:
Form_ID Ident_Type_ID
1 2
2 2
7 2
I know that form_id = 1 uses 1481 Ident_Type_IDs and Form_ID = 7 (the new form replacing form_id = 1) contains 1482 Ident_type_ids
This UNION query:
Select ident_type_id as Form1_IDs
from form_ident_types
where b.form_id = 1
UNION
Select ident_type_id as Form2_IDs
from form_ident_types
where form_id = 7
Returns 1785 rows so I know from basic math that there are 304 fields on the new form (1785 - 1481).
In Oracle I could do this query to return the rows that are different
Select ident_type_id as Form1_IDs
from form_ident_types
where b.form_id = 1
MINUS
Select ident_type_id as Form2_IDs
from form_ident_types
where form_id = 7
The result set would be the 304 rows that are not in form 1
I could write a stored proc to do this for me, but I would prefer to do it with a query. Since there is no MINUS equivalent in MS SQL 2000 what is/are my option(s)?
Any help would be appreciated.
[font="Comic Sans MS"]If life was easy, it wouldn't be any fun.[/font]
[font="Comic Sans MS"]Sean[/font]
October 11, 2007 at 8:20 am
Select ident_type_id as Form1_IDs
from form_ident_types
where b.form_id = 1
and ident_type_id NOT IN (
Select ident_type_id
from form_ident_types
where form_id = 7)
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 11, 2007 at 11:34 am
Jason,
Thanks for the smack in the face:D! Leave it to me to make something so easy so hard. Great Solution.
Sean
[font="Comic Sans MS"]If life was easy, it wouldn't be any fun.[/font]
[font="Comic Sans MS"]Sean[/font]
October 12, 2007 at 5:45 am
Sometimes your just TOO close to a problem to see a simple solution. I know I've been there many times!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 12, 2007 at 8:53 am
As an alternative to a NOT IN query, you could use a left outer join:
select t1.ident_type_id as Form1_IDs
from form_ident_types t1
left outer join form_ident_types t7
on t1.ident_Type_id = t7.ident_type_id
and t7.form_id = 7
where t1.form_id = 1
and t7.ident_type_id is null
The left outer join bit joins the table back onto itself for the ident_type_id rows for the different form_id value. Where there is a row for the first table and no match then the ident_type_id for the left outer join will be null.
If you ever need to do a 'NOT IN' for multiple columns then this works better than a sub select.
Jez
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply