Solved

SQL 2005 question

Posted on 2007-12-03
9
267 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Trigger selecting another database 4 37
Error when saving to sql table a '/' 5 28
Replace Dates in query 14 38
Help Required 2 32
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

770 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