Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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?
0
jtcy
Asked:
jtcy
  • 4
  • 2
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Shanmuga SundaramCommented:
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
 
imitchieCommented:
dmdq: exists does not need DISTINCT

select * from topic where exists (select * from faq where faq.topicid = topic.id)
0
 
dqmqCommented:
>exists does not need DISTINCT

You are correct.  
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now