Solved

Select parent data where child data is same as search info

Posted on 2008-10-25
10
251 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 92

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 92

Expert Comment

by:Patrick Matthews
ID: 22802914
Clausewitz,

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

Regards,

Patrick
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 92

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 92

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

739 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