|
|
Posted by schoultzy on 05/19/06 17:32
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
Navigation:
[Reply to this message]
|