Complicated query - select based on value

[jqq]

SQL2K on W2Kserver

I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)

The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
contact does not have that type then select types ('MN30D843J2',
'SC93JDL39D'). (Context - the current query selects two standard
address types "Main" and "Secondary"; we've added new data and now have
types "Alternate Main" and "Alternate Secondary". If the Contact has
Alternate addresses, I need to select those; if not, I need to select
the standard addresses. There are other address types in use, so I
must specify which types to select.)

Can anyone point me in the right direction?

Thanks very much! jamileh

CREATE TABLE [CONTACTS] (
[CONTACT_X] [char] (10),
[LONGNAME] [char] (75),
[ACTIVE] [bit])

CREATE TABLE [CONTACTADDRESSES] (
[CONTACT_X] [char] (10),
[ADDRESS_X] [char] (10),
[ADDRESSTYPE_REFX] [char] (10),
[ACTIVE] [bit])

CREATE TABLE [ADDRESSES] (
[ADDRESS_X] [char] (10),
[ADDRESSLINE1] [char] (60),
[ADDRESSLINE2] [char] (60),
[CITY] [char] (20),
[STATE] [char] (2),
[ZIPCODE] [char] (11),
[PHONE] [char] (10))

CREATE TABLE [REFERENCETABLE] (
[REFERENCETABLE_X] [char] (10),
[ADDRESS_X] [char] (10),
[DESCRIPTION] [char] (60))

CREATE TABLE [MASTERTABLE] (
[CONTACT_X] [char] (10),
[RECORDTYPE] [char] (1),
[ACTIVE] [bit])

CREATE VIEW vw_CONTACTInfo_ListLoc
AS
SELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,
CONTACTADDRESSES.ADDRESSTYPE_REFX,
Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,
CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,
ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,
ADDRESSES.ZIPCODE, ADDRESSES.PHONE
FROM CONTACTS INNER JOIN CONTACTADDRESSES ON
CONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOIN
ADDRESSES ON CONTACTADDRESSES.ADDRESS_X =
ADDRESSES.ADDRESS_X
INNER JOIN REFERENCETABLE Type_REFERENCETABLE ON
CONTACTADDRESSES.ADDRESSTYPE_REFX =
Type_REFERENCETABLE.REFERENCETABLE_X
WHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFX
IN
('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =
1)) AND
(CONTACTS.CONTACT_X IN
(SELECT CONTACT_X FROM MASTERTABLE WHERE
ACTIVE = 1 AND RECORDTYPE = 'E'))

[Simon Hayes]

"jqq" <jq*@myrealbox.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

SQL2K on W2Kserver

I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)

The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
contact does not have that type then select types ('MN30D843J2',
'SC93JDL39D'). (Context - the current query selects two standard
address types "Main" and "Secondary"; we've added new data and now have
types "Alternate Main" and "Alternate Secondary". If the Contact has
Alternate addresses, I need to select those; if not, I need to select
the standard addresses. There are other address types in use, so I
must specify which types to select.)

Can anyone point me in the right direction?

Thanks very much! jamileh

<snip>

The short answer is probably to see CASE in Books Online. If you need more
information, I suggest you provide some INSERT statements for sample data,
and also the output you expect - it's not very clear (to me) exactly what
your query should return.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon

[AK]

If the Contact has (INNER JOIN)
Alternate addresses, I need to select those;

UNION ALL
if not, NOT EXISTS(....)

I need to select
the standard addresses.

[--CELKO--]

Without better specs, this is hard. The tables had no keys; the names
of the data elements are awful, you even put physical storage and usage
into the names! You are using bit flags in SQL. There does not seem to
be any consistent design here. Clraning it up a bit, I got this:

CREATE TABLE Contacts
(contact_id CHAR(10) NOT NULL PRIMARY KEY,
long_name CHAR(75) NOT NULL);

CREATE TABLE Addresses
(address_id CHAR(10) NOT NULL PRIMARY KEY,
address_line1 CHAR(35) NOT NULL, -- usps lengths
address_line2 CHAR(35) NOT NULL,
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(9) NOT NULL,
phone_nbr CHAR(10) NOT NULL));

Your codes belong to the relationship, and not in their own tables,
something more like this

CREATE TABLE ContactAddresses
(contact_id CHAR(10) NOT NULL
REFERENCES Contacts (contact_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_id CHAR(10) NOT NULL
REFERENCES Addresses (address_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_type INTEGER NOT NULL, -- see suggestion below
PRIMARY KEY (contact_id, address_id, address_type)
contact_status CHAR(3) DEFAULT 'act' NOT NULL
CHECK (contact_status IN ('act', 'old', ..));

Views should be kept simple so they can be used in many place. And
unless they dela wiyth a Volkswagen, you do not prefix them with "vw_"
:)

CREATE VIEW ContactInfo (contact_id, long_name,
address_type, address_id,
address_line1, address_line2,
city_name, state_code, zip_code,
phone_nbr)
AS
SELECT C.contact_id, C.long_name,
CA.address_type, CA.address_id,
A.address_line1, A.address_line2,
A.city_name, A.state_code, A.zip_code,
A.phone_nbr
FROM Contacts AS C,
ContactAddresses AS CA,
Addresses AS A
WHERE CA.contact_id = C.contact_id
AND CA.address_id = A.address_id
AND CA.address_type BETWEEN 100 AND 299;

the current query selects two standard address types "Main" and "Secondary"; we've added new data and now have types "Alternate Main" and "Alternate Secondary". If the Contact has Alternate addresses, I need to select those; if not, I need to select the standard addresses. <<

You need a better encoding scheme than those awful ten-letter
nightmares. I read your narrative as meaning one contact can have only
one address of each type. Here is a hierarchical encoding suggestion,
with some room for growth.

100-199 = Main Address
110-119 = Alternative Main Address
200-299 = Secondary Address
210-219 = Alternative Secondary Address

The query would be something like this:

SELECT DISTINCT I1.*
FROM ContactInfo AS I1
WHERE address_type IN (200, 210) -- has secondary address
OR (address_type IN (100, 110) -- has main address
AND NOT EXISTS
(SELECT *
FROM ContactInfo AS I2
WHERE address_type IN (200, 210) -- no secondary address
AND I1.contact_id = I2.contact_id
AND I1.address_id = I2.address_id));

[jqq]

My apologies for leaving out the data, I didn't want to get too long in
my original post if it wasn't needed. Please see below (including one
fix on a table).

So, the current query will pull John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "standard" addresses
on Main, Second and Third streets.

The results I need would give John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
on Fifth and Sixth streets.

I've used CASE, but not to select multiple records based on one field.
I'm not sure how to make that work and I can't find anything in BOL to
explain it.

Thanks.
ALTER TABLE [REFERENCETABLE] DROP COLUMN [ADDRESS_X]

INSERT [CONTACTS] VALUES ('A1','John Smith',1)
INSERT [CONTACTS] VALUES ('B2','Frank Doe',1)
INSERT [CONTACTS] VALUES ('C3','Jane Jones',1)
INSERT [CONTACTS] VALUES ('D4','Susan Roe',0)
INSERT [CONTACTS] VALUES ('E5','George Brown',1)

INSERT [CONTACTADDRESSES] VALUES ('A1','F1','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','G2','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','H3','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','I4','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','J5','AM39DK3KD9',0)
INSERT [CONTACTADDRESSES] VALUES ('B2','K6','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','L7','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','M8','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','N9','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','O0','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','P1','ASKD943KDI',1)
INSERT [CONTACTADDRESSES] VALUES ('C3','Q2','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('D4','R3','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('E5','S4','AM39DK3KD9',1)

INSERT [ADDRESSES] VALUES ('F1','123 Main
St','','Anytown','PA','12345','5074951548')
INSERT [ADDRESSES] VALUES ('G2','456 Second St','Apt
9','Anytown','PA','45678','5074328548')
INSERT [ADDRESSES] VALUES ('H3','789 Third
St','','Anytown','PA','45678','5074321111')
INSERT [ADDRESSES] VALUES ('I4','987 Fourth
St','','Anytown','PA','12345','5074959999')
INSERT [ADDRESSES] VALUES ('J5','654 Fifth
St','','Anytown','PA','12345','5074955555')
INSERT [ADDRESSES] VALUES ('K6','1 Main
St','','Somewhere','UT','87654','2426831234')
INSERT [ADDRESSES] VALUES ('L7','2 Second St','Suite
600','Somewhere','UT','87654','2426835678')
INSERT [ADDRESSES] VALUES ('M8','3 Third
St','','Somewhere','UT','87654','2426839876')
INSERT [ADDRESSES] VALUES ('N9','4 Fourth
St','','Somewhere','UT','87654','2426835432')
INSERT [ADDRESSES] VALUES ('O0','5 Fifth
St','','Somewhere','UT','87654','2426831111')
INSERT [ADDRESSES] VALUES ('P1','6 Sixth
St','','Somewhere','UT','87654','2426839999')
INSERT [ADDRESSES] VALUES ('Q2','123 NoGood
St','','Nowhere','AK','98765','9051875135')
INSERT [ADDRESSES] VALUES ('R3','456 NotMe
St','','Nonesuch','CA','43210','7631584625')
INSERT [ADDRESSES] VALUES ('S4','789 UhOh
St','','Noway','GA','36847','6427892462')

INSERT [REFERENCETABLE] VALUES ('MN30D843J2','Standard Main')
INSERT [REFERENCETABLE] VALUES ('SC93JDL39D','Standard Secondary')
INSERT [REFERENCETABLE] VALUES ('AM39DK3KD9','Alternate Main')
INSERT [REFERENCETABLE] VALUES ('ASKD943KDI','Alternate Secondary')
INSERT [REFERENCETABLE] VALUES ('BL2309DD3L','Billing Only')

INSERT [MASTERTABLE] VALUES ('A1','E',1)
INSERT [MASTERTABLE] VALUES ('B2','E',1)
INSERT [MASTERTABLE] VALUES ('C3','N',1)
INSERT [MASTERTABLE] VALUES ('D4','E',1)
INSERT [MASTERTABLE] VALUES ('E5','E',0)

[jqq]

As I said, changing the database structure is right out. I didn't
build the beastie - it's the backend for a proprietary application.
The only thing I can do is pull data.

These views are for the exact purpose of selecting very specific sets
of data to provide to some websites. This one's not half bad, you
should see some of the others!

I did leave out PKs, etc. - sorry. The tables are actually much
bigger & I was trying to just pull the needed info for simplicity. If
there's something specific that would help, please let me know & I'll
post an update. In general, you're right and the "tablename_K" columns
are PKs and FKs.

As you can see in my last post (with data), one contact can have
multiple addresses of each type and I need to pull all addresses of the
correct types.

Any rate, thanks for the advice! j

p.s. What's wrong with bit flags?

[jqq]

I read this several times without any clue what you meant, but I think
it's beginning to permeate into my poor, bleeding braincells. I'll see
what I can come up with. Thanks!

[Erland Sommarskog]

jqq (jq*@myrealbox.com) writes:

p.s. What's wrong with bit flags?

Nothing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

jqq (jq*@myrealbox.com) writes:

My apologies for leaving out the data, I didn't want to get too long in
my original post if it wasn't needed. Please see below (including one
fix on a table).

So, the current query will pull John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "standard" addresses
on Main, Second and Third streets.

The results I need would give John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
on Fifth and Sixth streets.

This query gives the result described above, but does not match your
description in the first post. But maybe you simply messed up on all
these terrible 10-letter codes when you composed the sample data.

SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('MN30D843J2', 'SC93JDL39D')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')
AND NOT EXISTS (SELECT *
FROM CONTACTADDRESSES CA1
WHERE C.CONTACT_X = CA1.CONTACT_X
AND CA1.ADDRESSTYPE_REFX IN ('ASKD943KDI'))
UNION ALL
SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('AM39DK3KD9', 'ASKD943KDI')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

They are a proprietary, low level data type. The design is the way we
handled things with punch cards in the old days. Flags are usually
computed columns. It is usually better to invent a status code which
can be extended, or to capture the date of an event, etc.

[jqq]

Wow, I went off to read up on EXISTS and came back to find the query
all done!

That's exactly what I needed *and* I learned a new trick.

Thanks very much!!