1

In an interview, I was asked to find out the names of parents who have at least one child of each gender i.e. at least 1 male and 1 female. I solved that. Then they modified the question to find names of parents who have at least 2 male and 2 female children. This is the table: Table Name: Children

Here's the query that I used for the first ask:

SELECT distinct C1.Parent_Name 
FROM Children C1 
JOIN Children C2
  ON C1.Parent_Name = C2.Parent_Name
WHERE C1.Child_Gender = 'Male' AND C2.Child_Gender = 'Female';

Can you please help with the query for the second ask?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Super.Sam
  • 37
  • 1
  • 5
  • you can order by desc limit 2 then select the last record – guradio Nov 09 '18 at 08:53
  • 3
    Most people here want sample data (and expected result) as formatted text, not as images (or links to images.) – jarlh Nov 09 '18 at 09:09
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. PS I just googled 'SQL Query to find records that has at least 2' from your title for multiple hits--because people don't google. – philipxy Nov 09 '18 at 09:15
  • Possible duplicate of [SQL: Select rows with a column value that occurs at least N times?](https://stackoverflow.com/questions/5760335/sql-select-rows-with-a-column-value-that-occurs-at-least-n-times) – philipxy Nov 09 '18 at 09:17

3 Answers3

5

You can try to use condition aggregate function in HAVING

Schema (MySQL v5.7)

CREATE TABLE T(
   Parent_Name VARCHAR(50),
  Child_Gender varchar(50)
);

INSERT INTO T VALUES('David','Male');
INSERT INTO T VALUES('Emma','Male');
INSERT INTO T VALUES('David','Female');
INSERT INTO T VALUES('David','Female');
INSERT INTO T VALUES('Tom','Female');
INSERT INTO T VALUES('N','Male');
INSERT INTO T VALUES('A','Male');
INSERT INTO T VALUES('K','Male');
INSERT INTO T VALUES('David','Female');
INSERT INTO T VALUES('Emma','Female');
INSERT INTO T VALUES('Emma','Female');
INSERT INTO T VALUES('Emma',null);
INSERT INTO T VALUES('J','Male');
INSERT INTO T VALUES('D','Male');
INSERT INTO T VALUES('Emma','Male');

Query #1

Select
    Parent_Name
From Children  
GROUP BY
    Parent_Name
HAVING  
    COUNT(CASE WHEN Child_Gender='Male' THEN 1 END)>1
AND 
    COUNT(CASE WHEN Child_Gender='Female' THEN 1 END) > 1;

| Parent_Name |
| ----------- |
| Emma        |

View on DB Fiddle

D-Shih
  • 44,943
  • 6
  • 31
  • 51
0

A minor change to the query only, >= 2 instead of >= 1 :

SELECT parent_Name
FROM T  
GROUP BY parent_Name
HAVING COUNT(CASE WHEN Child_Gender = 'Male' THEN 1 END) >= 2
AND COUNT(CASE WHEN Child_Gender = 'Female' THEN 1 END) >= 2;
octano
  • 851
  • 1
  • 10
  • 18
George Joseph
  • 5,842
  • 10
  • 24
0

In SQL Server you can use such solution to get not only person but also details about children.

WITH CTE AS (

SELECT PivotTable.Parent_Name, 
       PivotTable.Female As nr_of_female_children,
       PivotTable.Male As nr_of_male_children
FROM
(SELECT #T.Parent_Name, #T.Child_Gender
    FROM #T) AS SourceTable
PIVOT
(
COUNT(Child_Gender)
FOR Child_Gender IN (Male,Female)
) AS PivotTable

)

SELECT *
FROM CTE
WHERE CTE.nr_of_female_children>1 AND CTE.nr_of_male_children >1
Andrzej M.
  • 92
  • 3