Stuck on a Query

1 Jul 2005 2:21 PM

Wayne Wengert

I have 3 tables involved

TblA
NameID int PK
EmailAddr varchar(65)
.....

TblB
ContactID int PK
NameID
....

TblC
EventID varChar(10) PK
Category1 int ' FK pointing to a TblB.ContactID
Category2 int ' FK pointing to a TblB.ContactID
Category3 int ' FK pointing to a TblB.ContactID
Category4 int ' FK pointing to a TblB.ContactID
Category5 int ' FK pointing to a TblB.ContactID

I want to return a distinct set of TblA.EmailAddr representing all the
"CategoryX" pointers in TblC for a select list of events (EventID)

I got myself all tangled up in a set of Inner Joins - I think my basic
approach was wrong.

Any suggestions are appreciated

Wayne

1 Jul 2005 2:37 PM

David Portas

I really don't like the design, it looks like an elementary mistake to
do it that way. How about:

CREATE TABLE TblC (eventid VARCHAR(10), category_no INTEGER NOT NULL
CHECK (category_no BETWEEN 1 AND 5), contactid INTEGER NOT NULL
REFERENCES TblB (contactid), PRIMARY KEY (eventid,category_no), UNIQUE
(eventid,contactid)) ;

SELECT A.emailaddr
FROM TblC AS C
JOIN TblB AS B
ON C.contactid = B.contactid
JOIN TblA AS A
ON B.nameid = A.nameid ;

Easier?

If not you'll presumably have to do 5 outer joins as well as the
DISTINCT.

--
David Portas
SQL Server MVP
--

1 Jul 2005 2:47 PM

Wayne Wengert

David;

Thanks for the quick response. Let me spend some time trying to understand
your suggested structure and see if I can apply it to my situation. You used
a couple of things (CHECK and REFERENCES) that I'm not familiar with so I
need to do some reading in BOL)

Wayne

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1120228654.983763.117970@g47g2000cwa.googlegroups.com...
>I really don't like the design, it looks like an elementary mistake to
> do it that way. How about:
>
> CREATE TABLE TblC (eventid VARCHAR(10), category_no INTEGER NOT NULL
> CHECK (category_no BETWEEN 1 AND 5), contactid INTEGER NOT NULL
> REFERENCES TblB (contactid), PRIMARY KEY (eventid,category_no), UNIQUE
> (eventid,contactid)) ;
>
> SELECT A.emailaddr
> FROM TblC AS C
> JOIN TblB AS B
> ON C.contactid = B.contactid
> JOIN TblA AS A
> ON B.nameid = A.nameid ;
>
> Easier?
>
> If not you'll presumably have to do 5 outer joins as well as the
> DISTINCT.
>
> --
> David Portas
> SQL Server MVP
> --
>