Querying one-to-many tables

[v_roma]

Hi,
I have a database with, for the sake of simplicity, two tables with a
one-to-many relationship. The first table is the user info and the second is
the users' expertise.

So, again for the sake of simplicity, the first table has the following data:
John Doe
Jane Doe

And the second table would have the following data:
John Doe, A topic
John Doe, B topic
Jane Doe, B topic
Jane Doe, C topic
Jane Doe, D topic

I am setting up a web site where the web user would be able to search for
people with expertise in certain topics. The problem I am having is when you
want to search for people with expertise in both the B and the C topic. I set
up my query so that I have an inner join between the two tables on the name but
I can't do topic = B AND topic=C or I get no results, obviously. But I also
can't do OR because then I get both Jane and John.

I realize that this is more an SQL question than Coldfusion and that there is
likely a very obvious answer that I am missing, but this is driving me crazy.
How do I write this query so that I get only people that have expertise in both
(or at least) B and C?

Any help is greatly appreciated,
v_roma

[mxstu]

v_roma,

Assuming your second table ("userTopics") does not allow duplicates, you
could use something like this:

SELECT u.UserName, COUNT(*) AS TopicNameCount
FROM user u INNER JOIN userTopics ut ON u.userName = ut.userName
WHERE ut.TopicName IN ('B topic', 'C topic')
GROUP BY u.UserName
HAVING COUNT(*) = 2

* Note - 2 is the number of topics you're matching (ie. B Topic AND C Topic)

Disclaimer: Obviously, this is a horrible query since it contains potential
reserved words (ie. User) and you wouldn't want to store the text value
"userName" as the FK in another table ;-)

[v_roma]

Thanks, mxstu, that worked great. I'm not sure what you meant by the second
table not allowing duplicates. There won't be any duplicate user/topic
combinations but there will be "duplicate" topics for different users. Either
way, your suggestion worked.

[mxstu]

>There won't be any duplicate user/topic combinations but there will be

>"duplicate" topics for different users.

Yes. That's exactly what I mean. No duplicate user/topic combinations.

[Dan Bracuk]

I'd use three tables for this - users, topics and users_topics. user_topics
would have a many to many relationship to both other tables. Then I could do
this (syntax is db specific of course)

select thefields
from users u join users_topics ut using (user_id)
join topics t using (topic_id)
where topic_id in ('B','C')




Originally posted by: v_roma
Hi,
I have a database with, for the sake of simplicity, two tables with a
one-to-many relationship. The first table is the user info and the second is
the users' expertise.

So, again for the sake of simplicity, the first table has the following data:
John Doe
Jane Doe

And the second table would have the following data:
John Doe, A topic
John Doe, B topic
Jane Doe, B topic
Jane Doe, C topic
Jane Doe, D topic

I am setting up a web site where the web user would be able to search for
people with expertise in certain topics. The problem I am having is when you
want to search for people with expertise in both the B and the C topic. I set
up my query so that I have an inner join between the two tables on the name but
I can't do topic = B AND topic=C or I get no results, obviously. But I also
can't do OR because then I get both Jane and John.

I realize that this is more an SQL question than Coldfusion and that there is
likely a very obvious answer that I am missing, but this is driving me crazy.
How do I write this query so that I get only people that have expertise in both
(or at least) B and C?

Any help is greatly appreciated,
v_roma

[mxstu]

Originally posted by: Dan Bracuk
I'd use three tables for this - users, topics and users_topics. .....

Based on the OP's comment "... again for the sake of simplicity, the first
table has the following data ...., I would assume that is not their real (or
complete) table structure.