StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL: Multiple subqueries and where in join clauses
primarykey
Id
20543661
data
AcceptedAnswerId
20543874
AnswerCount
1
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-12-12T12:32:04.627
FavoriteCount
0
LastActivityDate
2013-12-12T13:30:20.237
LastEditDate
2013-12-12T13:30:20.237
LastEditorUserId
1266527
OwnerUserId
1266527
ParentId
0
PostTypeId
1
Score
0
ViewCount
226
LastEditorDisplayName
text
Body
So I have three tables; <code>grandparent</code>, <code>parent</code>, and <code>child</code>. <code>parent</code> and <code>child</code> each have a <code>parent</code> field. I want to find all the grandchildren of the grandparent where all the grandchildren are married and the grandparent is over 80. Lets assume each grandparent can only have 4 grandchildren. So I start with a select on the grandparent table: <pre><code>select id from grandparent where age > 80 </code></pre> This will give me a set of grandparents whose age is greater than 80. Then I find the parents in the parent table grouped by their grandparent; <pre><code>select group_concat(id) as grandparent from parent where parent in ( select id from grandparent where age > 80 ) group by parent </code></pre> This gives me a set of parents for each row of grandparents, something like <pre><code>grandparent: (1,2,3,4) grandparent: (5,6,7,8) grandparent: (9,10,11,12) </code></pre> Now I want to find all the children of these parents where they are all married, so far I have something like; <pre><code>select group_concat(parent) as parent group_concat(id) as children from child where parent in ( select group_concat(id) as grandparent from parent where parent in ( select id from grandparent where age > 80 ) group by parent ) and relationship_status = "MARRIED" having count(id) = 4; </code></pre> But obviously it doesn't work because I need the in clause to search the sets of each row returned from the previous query. How would I run the above query for each row set? Note: These aren't the real names of my tables or fields, just removing domain knowledge so it's hopefully easier to help.
Tags
<mysql><join><subquery><where-in>
Title
MySQL: Multiple subqueries and where in join clauses
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USAutonomy
UserOwnerUserId
1. USAutonomy
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.