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 [CONTACTADDRESSE S] (
[CONTACT_X] [char] (10),
[ADDRESS_X] [char] (10),
[ADDRESSTYPE_REF X] [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.CONTAC T_X, CONTACTS.LONGNA ME,
CONTACTADDRESSE S.ADDRESSTYPE_R EFX,
Type_REFERENCET ABLE.DESCRIPTIO N AS Type_DESCRIPTIO N,
CONTACTADDRESSE S.ADDRESS_X, ADDRESSES.ADDRE SSLINE1,
ADDRESSES.ADDRE SSLINE2, ADDRESSES.CITY, ADDRESSES.STATE ,
ADDRESSES.ZIPCO DE, ADDRESSES.PHONE
FROM CONTACTS INNER JOIN CONTACTADDRESSE S ON
CONTACTS.CONTAC T_X = CONTACTADDRESSE S.CONTACT_X INNER JOIN
ADDRESSES ON CONTACTADDRESSE S.ADDRESS_X =
ADDRESSES.ADDRE SS_X
INNER JOIN REFERENCETABLE Type_REFERENCET ABLE ON
CONTACTADDRESSE S.ADDRESSTYPE_R EFX =
Type_REFERENCET ABLE.REFERENCET ABLE_X
WHERE (CONTACTS.ACTIV E = 1) AND (CONTACTADDRESS ES.ADDRESSTYPE_ REFX
IN
('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESS ES.ACTIVE =
1)) AND
(CONTACTS.CONTA CT_X IN
(SELECT CONTACT_X FROM MASTERTABLE WHERE
ACTIVE = 1 AND RECORDTYPE = 'E'))

[Simon Hayes]

"jqq" <jq*@myrealbox. com> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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 ContactAddresse s
(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,
ContactAddresse s 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 [CONTACTADDRESSE S] VALUES ('A1','F1','MN3 0D843J2',1)
INSERT [CONTACTADDRESSE S] VALUES ('A1','G2','SC9 3JDL39D',1)
INSERT [CONTACTADDRESSE S] VALUES ('A1','H3','SC9 3JDL39D',1)
INSERT [CONTACTADDRESSE S] VALUES ('A1','I4','BL2 309DD3L',1)
INSERT [CONTACTADDRESSE S] VALUES ('A1','J5','AM3 9DK3KD9',0)
INSERT [CONTACTADDRESSE S] VALUES ('B2','K6','MN3 0D843J2',1)
INSERT [CONTACTADDRESSE S] VALUES ('B2','L7','SC9 3JDL39D',1)
INSERT [CONTACTADDRESSE S] VALUES ('B2','M8','SC9 3JDL39D',1)
INSERT [CONTACTADDRESSE S] VALUES ('B2','N9','BL2 309DD3L',1)
INSERT [CONTACTADDRESSE S] VALUES ('B2','O0','AM3 9DK3KD9',1)
INSERT [CONTACTADDRESSE S] VALUES ('B2','P1','ASK D943KDI',1)
INSERT [CONTACTADDRESSE S] VALUES ('C3','Q2','AM3 9DK3KD9',1)
INSERT [CONTACTADDRESSE S] VALUES ('D4','R3','AM3 9DK3KD9',1)
INSERT [CONTACTADDRESSE S] VALUES ('E5','S4','AM3 9DK3KD9',1)

INSERT [ADDRESSES] VALUES ('F1','123 Main
St','','Anytown ','PA','12345', '5074951548')
INSERT [ADDRESSES] VALUES ('G2','456 Second St','Apt
9','Anytown','P A','45678','507 4328548')
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','','Somewhe re','UT','87654 ','2426831234')
INSERT [ADDRESSES] VALUES ('L7','2 Second St','Suite
600','Somewhere ','UT','87654', '2426835678')
INSERT [ADDRESSES] VALUES ('M8','3 Third
St','','Somewhe re','UT','87654 ','2426839876')
INSERT [ADDRESSES] VALUES ('N9','4 Fourth
St','','Somewhe re','UT','87654 ','2426835432')
INSERT [ADDRESSES] VALUES ('O0','5 Fifth
St','','Somewhe re','UT','87654 ','2426831111')
INSERT [ADDRESSES] VALUES ('P1','6 Sixth
St','','Somewhe re','UT','87654 ','2426839999')
INSERT [ADDRESSES] VALUES ('Q2','123 NoGood
St','','Nowhere ','AK','98765', '9051875135')
INSERT [ADDRESSES] VALUES ('R3','456 NotMe
St','','Nonesuc h','CA','43210' ,'7631584625')
INSERT [ADDRESSES] VALUES ('S4','789 UhOh
St','','Noway', 'GA','36847','6 427892462')

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****@sommarsk og.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_DESCRIPTIO N, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSE S 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.REFERENCETABL E_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 CONTACTADDRESSE S 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_DESCRIPTIO N, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSE S 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.REFERENCETABL E_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****@sommarsk og.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.