Solved

Select parent data where child data is same as search info

Posted on 2008-10-25
10
258 Views
Last Modified: 2012-05-05
I have two tables.

Table 1: ForumList, (parent)
ForumListId, parent
ForumListName

Table 2: Forum, (child)
ForumId
ForumListId
ForumText

Now I want to select all ForumList where the child Forum have ForumText = search string (use LIKE).
The SQL result have to be the table ForumList, like SELECT * FROM ForumList ....
How to do that?
0
Comment
Question by:KimGordon
[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
  • 3
  • 3
10 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 100 total points
ID: 22802764
SELECT fl.ForumListID, fl.ForumListName, f.ForumID, f.ForumText
FROM ForumList fl INNER JOIN
    Forum f ON fl.ForumListID = fl.ForumListID
WHERE f.ForumText LIKE '%foo%'
0
 
LVL 2

Accepted Solution

by:
Clausewitz earned 150 total points
ID: 22802776
Just use a subquery.
SELECT * FROM ForumList WHERE ForumListId IN
(
  SELECT DISTINCT ForumListId FROM Forum WHERE ForumText LIKE -- your search term
)

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22802914
Clausewitz,

That will work, but won't that be less efficient than a join?

Regards,

Patrick
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:KimGordon
ID: 22803121
I'm using the subquery alternative now and it works!
Thanks!

0
 

Author Comment

by:KimGordon
ID: 22803134
Is this solution less efficient?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22803159
Have you tried my suggestion?
0
 

Author Comment

by:KimGordon
ID: 22803304
No, should I? Is this one better?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 22803355
KimGordon said:
>>No, should I?

Yes, you should.

>>Is this one better?

That is for you to decide.  On large data sets, my query will tend to run faster.
0
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22803402
i think the subquery will be faster, because a join should be more expensive.
Though the KimGordon doesn't need any data fields from the child table a join has no benefits over a subquery.
0
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22803469
Best you run both queries and look at the execution plan and the execution time to compare both queries to be sure which one runs faster.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

626 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