|
|
Posted by Erland Sommarskog on 05/20/06 00:40
schoultzy (schoultzy@yahoo.com) writes:
> This is probably a simple fix so be kind when/if you reply. The query
> below retrieves information for individuals based on a column named
> ATTRIB_DEF, and assorted other columns; however, the ATTRIB_DEF column
> is important one. Currently the query gets all individuals that have
> an ATTRIB_DEF that contains the string 'AC1' at the beginning of the
> entry. I want the query to do the opposite. I want all of the
> individuals who do not have an ATTRIB_DEF of 'AC1'. The simple fix
> would be to change the LIKE to NOT LIKE, however, the problem is that
> there are multiple ATTRIB_DEF entries for a single individual. In
> other words, one person can have several rows in the table and the only
> difference in the rows would be the value of ATTRIB_DEF. Simply
> changing the LIKE to NOT LIKE will return, along with those individuals
> without an ATTRIB_DEF of 'AC1', all the individuals with an ATTRIB_DEF
> of 'AC1' who also have ATTRIB_DEF entries that are not 'AC1'. I want
> all the individuals who do not have an ATTRIB_DEF of 'AC1' in any of
> their rows. I hope that I was clear enough. Thanks for you help in
> advance. The SQL Query follows:
You need a NOT EXISTS. Without full knowledge about keys, it's hazardous
to write the exact query, but I hope the below gives you a hint.
I've also performed the following modifications to the query:
1) I've rewritten the query to use the modern ANSI JOIN syntax. You were
using the old outer-join operator *= which is deprecated, and which
only works in SQL 2005 under a compatibility setting.
2) I've introduced aliases, as they in my opinion make the query easier to
read.
3) I've removed superfluous parentheses, which made it difficult to see
the forest for all the trees.
4) Some more minor things.
Finally: it looks funny with a column from attribute_trans in the ORDER
BY clause, when there is no colunm in the SELECT list.
SELECT td.table_desc, nm.preferred_name, nm.last_name,
am.addr_line_1, am.addr_line_2, am.addr_line_3,
am.city, am.state, am.zip, tc.table_desc, hxd.table_desc,
nm.id_num
FROM address_master am
JOIN name_master nm ON nm.id_num = am.id_num
AND nm.current_address = am.addr_cde
JOIN attribute_trans at ON at.id_num = nm.id_num
LEFT JOIN table_country tc ON am.country = tc.table_value
LEFT JOIN table_detail td ON nm.prefix = td.table_value
LEFT JOIN hdx_table_suffix_v hdx ON nm.suffix = hxd.table_value,
JOIN candidate c ON nm.id_num = c.id_num
JOIN candidacy cy ON cy.id_num = c.id_num
AND c.cur_yr = cy.yr_cde
AND c.cur_trm = cy.trm_cde
WHERE td.column_name = 'prefix'
AND at.attrib_begin_dte is NULL
-- AND at.attrib_def LIKE 'AC1%'
AND (coalesce(nm.stop_all_mail, 'N') = 'N')
AND c.cur_yr = '2007'
AND c.cur_stage = '02'
AND cy.candidacy_type IN ('F', 'U')
AND c.udef_1a_1 <> ''
AND cy.cur_candidacy = 'Y'
AND NOT EXISTS (SELECT *
FROM attribute_trans at2
WHERE at.id_num = at2.id_num)
ORDER BY at.attrib_cde ASC, am.zip ASC, nm.last_name ASC
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|