HOWTO: Join 3 Tables, Compare Unequals To 1 Table

[Google Mike]

I have a tough SQL join query and I figured it out, so I thought you
might find it useful.

I have 3 tables with tracking numbers in them. I want to think of the
sum of all these tracking numbers, then find non-matches to a master
tracking number table. When I find non-matches (orphans) in the master
tracking number table, I want to eliminate the orphans.

The master table is "master_tn". The column in question is "tn"
(tracking number).

((select tn from master_tn except select tn from opentickets) except
select tn from voidedtickets) except select tn from resolvedtickets

There you go. Enjoy!

If anyone can optimize that even better, I would welcome it.

[Google Mike]

go********@hotpop.com (Google Mike) wrote in message

I have a tough SQL join query and I figured it out, so I thought you
might find it useful.

I have 3 tables with tracking numbers in them. I want to think of the
sum of all these tracking numbers, then find non-matches to a master
tracking number table. When I find non-matches (orphans) in the master
tracking number table, I want to eliminate the orphans.

The master table is "master_tn". The column in question is "tn"
(tracking number).

((select tn from master_tn except select tn from opentickets) except
select tn from voidedtickets) except select tn from resolvedtickets

There you go. Enjoy!

If anyone can optimize that even better, I would welcome it.

Of course, that merely shows you how to select the data. To delete it,
you have to do:

delete from master_tn where tn in (((select tn from master_tn except
select tn from opentickets) except select tn from voidedtickets)
except select tn from resolvedtickets)