Having problems getting Duplicates with my queries...

[Rexolio]

I have 3 tables that go like this (more or less):

TABLE 1
ID     NAME     CITY     ST
1      J. Doe   Boise    ID
2      W. Smith Tampa    FL
3      B. Jones Atlanta  GA

TABLE 2
ID     SPOUSE   INCOME   DATE
1      Mary     55000    1/1/2004
2      Bob      74000    1/14/2004
3      Tina     60000    1/15/2004

TABLE 3
ID     CATEGORYID
1      1
1      3
2      2
2      3
2      4
3      1
3      2
3      5
3      6

The Primary key in Tables 1 & 2 are ID. These are the same and are created when a new record is entered into Table 1. The PK's in Table 3 are ID and CategoryID. ID holds all of the records together in a relationship.

The problem I'm having is I need be able to do a query on all 3 tables looking for a number of things, but in the end I only want 1 record. The problem I'm having is that when I do a search for categories where, for example, CategoryID = 1, 3 or 6, I get back duplicates. In this case, with the records above, I would get back 2 records for ID 1, 1 record for ID 2, and 2 records for ID 3. I don't want duplicates. Here is the query:

SELECT A.*, B.*, C.* FROM TABLE1 A
INNER JOIN TABLE2 B ON A.ID = B.ID
INNER JOIN TABLE3 C ON A.ID = C.ID
WHERE (A.ST <> 'TN') AND (B.DATE >= '1/1/2004') AND
(C.CATEGORYID = 1 OR C.CATEGORYID = 3 OR C.CATEGORYID = 6)
ORDER BY A.DATE

This query gives me duplicates. I've even tried saying:

...WHERE (A.ST <> 'TN') AND (B.DATE >= '1/1/2004') AND
(C.CATEGORYID = 1) 
OR (A.ST <> 'TN') AND (B.DATE >= '1/1/2004') AND
(C.CATEGORYID = 3) 
OR (A.ST <> 'TN') AND (B.DATE >= '1/1/2004') AND
(C.CATEGORYID = 6)
ORDER BY A.DATE

and of course that doesn't work either. Thanks in advance.


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[TJRTech]

Customer with ID of 1 will be returned twice because they are associated with Category 1 and 3 through the associative table 3. That is why you get the "dupes".

It is doing exactly what it is being instructed given the data.

What does it mean for a customer to be in two different categories, and is that "valid" in your problem domain?

TR

 

[Rexolio]

TR,

Thanks for the info. I realize why its happening, I'm just not sure how to do write the query to do what it is I want it to do.

In this case, there are categories for hobbies, like:

CATEGORYID  CATEGORY
1           Swimming
2           Cooking
3           Hiking
4           Movies
5           Music
6           Sports

There are about 20 categories total. I want to be able to search for people who are interested in multiple cateories/hobbies, but I don't want to get one record for every hobby they've selected.

Any ideas?


rexolio@yahoo.com
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes<br><br>"They got a pepper bar!" -- Quizno's Spongemonkeys

 

[TJRTech]

Okay, then link all three of the tables together, and compute (aggregate) a column for the number of Categories (Hobbies) they like; something like (untested):

SELECT A.*, B.*, SUM(C.*) AS NUM_HOBBIES
FROM TABLE1 A
INNER JOIN TABLE2 B ON A.ID = B.ID
INNER JOIN TABLE3 C ON A.ID = C.ID
WHERE (A.ST <> 'TN') AND (B.DATE >= '1/1/2004') AND
(C.CATEGORYID = 1 OR C.CATEGORYID = 3 OR C.CATEGORYID = 6)
GROUP BY C.CATEGORYID
ORDER BY A.DATE

 

[SQLSister]

BReak it down into steps.

First find the people with multiple hobbies

Select distinct table1.id from table1 join table2 on
table1.ID = table2.id
where count(table2.hobby) >1

Then join this derived table to your main table and select the records

Select table1.* from table1 join
( Select distinct table1.id from table1 join table2 on
table1.ID = table2.id
where count(table2.hobby) >1) a
on table1.id = a.id

 

[TJRTech]

Actually, in the last post of mine, you want to GROUP BY all columns in A, and B, and none of those in C.

Sorry.

TR

 

[sunila7]

Try these..

Select [ID],Max(T1.Name) [Name],Max(T1.City), Max(T1.ST), Max(T2.Spouse) Spouse, Max(T2.Income) Income, Max(t2.Date) [Date],Count(T3.CATEGORYID)
From Table1 T1
INNER JOIN Table2 T2 ON T1.ID= T2.ID
INNER JOIN Table3 T3 ON T1.ID=T3.ID
Group By [ID]
Having Count(T3.CATEGORYID) > 1


Select * from
(Select [ID],Max(T1.Name) [Name],Max(T1.City), Max(T1.ST), Max(T2.Spouse) Spouse, Max(T2.Income) Income, Max(t2.Date) [Date],Count(T3.CATEGORYID) CNT
From Table1 T1
INNER JOIN Table2 T2 ON T1.ID= T2.ID
INNER JOIN Table3 T3 ON T1.ID=T3.ID
Group By [ID]) TBL
Where CNT > 1

Sunil