select parent where more than one child
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
July 15, 2009 1:01 AM
UPDATED:
September 2, 2011 4:18 AM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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