SQL 2005 question

I have these two tables:

Topics and FAQ
A faq has an faq_topic_fk foreign key.

I want to select topics that have faq's topic_fk assigned. For example:

Topic
-----------
t1
t2
t3
t4

FAQ
------------
f1(FK=t1)
f2(FK=t1)
f3
f4(FK=t3)

The result will be t1 and t3.

How can I do that in select statement?
jtcyAsked:
Who is Participating?
 
imitchieCommented:
dmdq: exists does not need DISTINCT

select * from topic where exists (select * from faq where faq.topicid = topic.id)
0
 
dqmqCommented:
An inner join will filter out the topics without faq's:

select distinct topic.* from topic inner join faq on faq.topicID = topic.topicID from
0
 
dqmqCommented:
Oops,  this is better:

select distinct topic.* from topic inner join faq on faq.topicID = topic.topicID
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
imitchieCommented:
select * from topic where exists (select * from faq where faq.topicid = topic.id)

"exists" vs "inner join" vs "not in" are three different ways you can do it, each one can be the fastest at times depending on the the balance of # of records between faq and topic
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
select * from topic t, faq f where f.topicid=t.topicid
0
 
dqmqCommented:
Sure, all those styles work.  But you do need a distinct, group by, or union query to keep a topic from repeating once for each faq.
0
 
dqmqCommented:
>exists does not need DISTINCT

You are correct.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.