select parent where more than one child

5 pts.
Tags:
SELECT statement
SQL
SQL queries
I want to select a row from the parent table based on whether there are two or more rows in a child table. What is the best way to do that?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Queries like this can get tricky depending on your table design.

If your table looks like this:
<pre>CREATE TABLE MyTable
(ParentId INT,
ParentSequence INT, /*A number which is unique per parent*/
Value INT)</pre>

You can do something like this

<pre>SELECT *
FROM ParentTable
WHERE EXISTS (SELECT * FROM MyTable WHERE MyTable.ParentId = ParentTable.ParentId AND ParentSequence = 1)
AND EXISTS (SELECT * FROM MyTable WHERE MyTable.ParentId = ParentTable.ParentId AND ParentSequence = 2)</pre>

If you want to return the records that simply have 2 or more records in the MyTable table then something like this would work.

<pre>SELECT *
FROM ParentTable
JOIN (SELECT ParentId, count(*) ct
FROM MyTable
GROUP BY ParentId
HAVING count(*) >= 2) a ON a.ParentId = ParentTable.ParentId</pre>

well ur question is not clear, if u r a beginner,u might trouble to get the details from a table,
to retrive all columns and rows information

type the query “select * from parent;”,

if u need to get the particular column or row information u need the condition to retrive the data as “select age from parent where age>40;” this is the code to retrive

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • shiva3322
    well ur question is not clear, if u r a beginner,u might trouble to get the details from a table, to retrive all columns and rows information type the query "select * from parent;", if u need to get the particular column or row information u need the condition to retrive the data as "select age from parent where age>40;" this is the code to retrive
    660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following