Hello Everyone,

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:

SELECT table_detail.table_desc,
name_master.preferred_name,
name_master.last_name,
address_master.addr_line_1,
address_master.addr_line_2,
address_master.addr_line_3,
address_master.city,
address_master.state,
address_master.zip,
table_country.table_desc,
hdx_table_suffix_v.table_desc,
name_master.id_num
FROM address_master,
attribute_trans,
name_master,
table_country,
table_detail,
hdx_table_suffix_v,
candidate,
candidacy
WHERE ( address_master.country *= table_country.table_value ) and
( name_master.prefix *= table_detail.table_value ) and
( name_master.suffix *= hdx_table_suffix_v.table_value ) and
( attribute_trans.id_num = name_master.id_num ) and
( name_master.id_num = address_master.id_num ) and
( name_master.current_address = address_master.addr_cde ) and

( name_master.id_num = candidate.id_num ) and
( candidacy.id_num = candidate.id_num ) and
( candidate.cur_yr = candidacy.yr_cde ) and
( candidate.cur_trm = candidacy.trm_cde ) and
( ( table_detail.column_name = 'prefix' ) AND
( attribute_trans.attrib_begin_dte is NULL ) AND

( attribute_trans.attrib_def like 'AC1%' ) AND

(name_master.stop_all_mail is NULL OR
name_master.stop_all_mail = 'N') AND
candidate.cur_yr = '2007' AND
candidate.cur_stage = '02' AND
(candidacy.candidacy_type = 'F' OR
candidacy.candidacy_type = 'U') AND
candidate.udef_1a_1 <> '' ) and
candidacy.cur_candidacy = 'Y'
ORDER BY attribute_trans.attrib_cde ASC,
address_master.zip ASC,
name_master.last_name ASC

Untested reply:

You would need 2 queries, first will be the same one that you have in
the select list, the other inner query will basically return you the
list of individuals who have the 'AC1' in their ATTRIB_DEF field.

select .... from ..
where individual not in (select disintct individual from ... where
ATTRIB_DEF like '%AC1%')

Hope that helps.

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx