Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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?
0
KimGordon
Asked:
KimGordon
  • 4
  • 3
  • 3
2 Solutions
 
Patrick MatthewsCommented:
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
 
ClausewitzCommented:
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 MatthewsCommented:
Clausewitz,

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

Regards,

Patrick
0
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!

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now