Solved

SQL 2005 question

Posted on 2007-12-03
9
254 Views
Last Modified: 2010-03-19
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
Comment
Question by:jtcy
  • 4
  • 2
9 Comments
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
Oops,  this is better:

select distinct topic.* from topic inner join faq on faq.topicID = topic.topicID
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:Shanmuga Sundaram
Comment Utility
select * from topic t, faq f where f.topicid=t.topicid
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
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
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
Comment Utility
dmdq: exists does not need DISTINCT

select * from topic where exists (select * from faq where faq.topicid = topic.id)
0
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
>exists does not need DISTINCT

You are correct.  
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now