UNION Query Question

[crbutler01]

I've got a union query that we use to generate an internal email list.
See below. To accommodate people requesting to be taken off, I'm
trying to figure out a way to incorporate some code that would EXCLUDE
certain prsn_person_id. So I guess I'm trying to do the opposite of a
union query. I want to take the list that generates from the union
query below and say, OK, now exclude anyone that is one an exclusion
list that we maintain. The exclusion list is in the form of a table
that is setup just like the other tables already being used in the
UNION query. Does anyone have any insight?



SELECT [prsn_person_id] from [dev_qry_masteremaillist_kitreq]
UNION Select [prsn_person_id] from [dev_qry_masteremaillist_repsusing]
UNION Select [prsn_person_id] from
[dev_qry_masteremaillist_repsusing_withspl]
UNION Select [prsn_person_id] from [rep_aig_add_to_email_list]
UNION Select [prsn_person_id] from [dev_qry_masteremaillist_repcodes];

 

[David Lloyd]

One alternative is to do an outer join of your union query with the
Exceptions table. For example:

SELECT [prsn_person_id]
FROM MyUnionQuery LEFT JOIN MyExceptionsTable ON
MyUnionQuery.[prsn_person_id]=MyExceptionsTable.[prsn_person_id]
WHERE MyExceptionsTable.[prsn_person_id] IS NULL

This will give you a list of the [prsn_person_id] numbers where there is no
match in the Exceptions table.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I've got a union query that we use to generate an internal email list.
See below. To accommodate people requesting to be taken off, I'm
trying to figure out a way to incorporate some code that would EXCLUDE
certain prsn_person_id. So I guess I'm trying to do the opposite of a
union query. I want to take the list that generates from the union
query below and say, OK, now exclude anyone that is one an exclusion
list that we maintain. The exclusion list is in the form of a table
that is setup just like the other tables already being used in the
UNION query. Does anyone have any insight?



SELECT [prsn_person_id] from [dev_qry_masteremaillist_kitreq]
UNION Select [prsn_person_id] from [dev_qry_masteremaillist_repsusing]
UNION Select [prsn_person_id] from
[dev_qry_masteremaillist_repsusing_withspl]
UNION Select [prsn_person_id] from [rep_aig_add_to_email_list]
UNION Select [prsn_person_id] from [dev_qry_masteremaillist_repcodes];

 

[crbutler01]

Worked Perfectly. Thanks.