Select parent data where child data is same as search info

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?
KimGordonAsked:
Who is Participating?
 
ClausewitzConnect With a Mentor Commented:
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
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
Clausewitz,

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

Regards,

Patrick
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
KimGordonAuthor Commented:
I'm using the subquery alternative now and it works!
Thanks!

0
 
KimGordonAuthor Commented:
Is this solution less efficient?
0
 
Patrick MatthewsCommented:
Have you tried my suggestion?
0
 
KimGordonAuthor Commented:
No, should I? Is this one better?
0
 
Patrick MatthewsCommented:
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
 
ClausewitzCommented:
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
 
ClausewitzCommented:
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
All Courses

From novice to tech pro — start learning today.