A simple SQL query for MYSQL is not working - Stack Overflow

admin管理员组

文章数量:1293693

We have a simple table with the following columns:

• userid (unique key)
• name
• dateofbirth
• parentid

If the user is a minor, then parentid contains the userid of the parent. Otherwise it is NULL.

Need to generate a list of all parents(adults) with their children (minors). One parent may have multiple children. Each person has a unique userid - unique key in the table.

We have approx 50K records. So it's not feasible to do it manually.

Hopefully it is not too complicated for an expert. Greatly appreciate any help!! TIA

I tried the following:

SELECT uu.userid, uu.name, uu.dateofbirth, uu.parentid
FROM usertable uu
WHERE uu.userid = (SELECT pp.userid
                   FROM usertable pp
                   WHERE pp.userid = uu.parentid);

It generates one row which is not what I need. I need the following:

Example table contents: enter image description here

Then my query should fetch all records except the first one. The first one has an adult with no children - I don't need that. I need all parents and their children only.

本文标签： A simple SQL query for MYSQL is not workingStack Overflow