Solved

SQL 2005 question

Posted on 2007-12-03
9
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
9 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 20401135
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
ID: 20401141
Oops,  this is better:

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

Expert Comment

by:imitchie
ID: 20401759
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
Independent Software Vendors: 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!

 
LVL 17

Expert Comment

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

Expert Comment

by:dqmq
ID: 20404686
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
ID: 20405728
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
ID: 20417707
>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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Why is the output of this function is like this? 4 47
kill process lock Sql server 9 81
SQL Error - Query 6 55
SQL Server 2005 database messed up. Can it be fixed? 4 39
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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