Solved

Select parent data where child data is same as search info

Posted on 2008-10-25
10
221 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now