Click here to Skip to main content
14,931,001 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello, I am not very well familiar with TSQL. I was working on a solution and fall into a problem.

I have a table structure like this:

SQL
CREATE TABLE Family (
  Id INT NOT NULL PRIMARY KEY,
  MotherId INT REFERENCES Family(Id),
  FatherId INT REFERENCES Family(Id),
  Name VARCHAR(30) NOT NULL,
  Age INT NOT NULL
);

With this record present in the table:
SQL
INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(1, NULL, NULL, 'David', 65);
    INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(2, NULL, NULL, 'Keti', 55);
    INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(3, 2, 1, 'Crysti', 25);
    INSERT INTO people(Id, MotherId, FatherId, Name, Age) VALUES(4, 2, 1, 'Ellen', 18);

What I want to achieve is that get the youngest child for each parent. Like in the above example data, the expected result should be:

SQL
Name      Age
=============
David    25
Keti      25


Thanks in advance for your help.

What I have tried:

I have tried multiple solutions like UNIONS, JOINS, with CTE (...) etc but all in vain. As I earlier mentioned, I'm not an expert in writing TSQL queries.
Posted
Updated 5-May-21 2:36am
Comments
Meysam Tolouee 17-Sep-18 7:35am
   
But youngest child is 18 not 25!

Your data and/or expected result is wrong: 18 is the youngest child in both cases for that data.

Start by finding the youngest child of each parent:
SQL
SELECT MotherID, MIN(Age) As Age FROM People GROUP BY MotherID HAVING MotherID IS NOT NULL;

SELECT FatherID, MIN(Age) As Age FROM People GROUP BY FatherID HAVING FatherID IS NOT NULL;
Then use JOIN to get the parent name:
SQL
SELECT a.Name, b.Age FROM People a
JOIN (SELECT MotherID, MIN(Age) As Age FROM People GROUP BY MotherID HAVING MotherID IS NOT NULL) b ON a.ID = b.MotherId;

SELECT a.Name, c.Age FROM People a
JOIN (SELECT FatherID, MIN(Age) As Age FROM People GROUP BY FatherID HAVING FatherID IS NOT NULL) c ON a.ID = c.FatherId;
You can then use a UNION to combine the two result sets.
   
You can use CTE too:

SQL
WITH Fathers
    AS (SELECT  F.FatherId, MIN(F.Age) Age
          FROM  dbo.Family AS F
         WHERE  F.FatherId IS NOT NULL
         GROUP BY F.FatherId), Mothers
    AS (SELECT  F.MotherId, MIN(F.Age) Age
          FROM  dbo.Family AS F
         WHERE  F.MotherId IS NOT NULL
         GROUP BY F.MotherId)
SELECT  Fathers.FatherId AS ParentId, Fathers.Age
  FROM  Fathers
UNION ALL
SELECT  Mothers.MotherId, Mothers.Age
  FROM  Mothers;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900