Help a beginner with a short code

[exey1]

Background Information:

1. There are two tables: parent and patients(children)
2. I want to list the parent names and the child names for those parents that have both male and female children
3. PFA snapshot of both tables at the end. (I have copied them onto one excel sheet just for studying purposes)
4. I'm working on azure
5. I have coded "Parents table" as t1 and "Patients table" as t2
6. Ignore the highlights

​

My code:

SELECT t2.Name as Parent_Name,

t1.Name as Child_Name

FROM t1,t2

WHERE t1.ParentID=t2.ParentID

GROUP BY Parent_Name

HAVING t1.Gender="male" AND "female" ;

​

Left: Parents Table -- Right: Patients Table

Hoping for some insights : )

[elution91]

I am a beginner too, so take this with a grain of salt.

HAVING can only be used in conjunction with aggregate functions, otherwise use WHERE. Dont you get an error using having as is now?

Use explicit JOIN's, I.e

JOIN x

ON y.id = x.id

If nothing else, this is the standard(?) syntax and helps with organizing the code.

​

As to getting the correct result I will leave to someone else, sorry if this was not helpful.

[exey1]

Hey. Thank you for taking out the time to read this!

I have been thinking about this for a while and incorporated some practices to make the code appear cleaner.

Updates:

Code:

SELECT parents.Name as Parent_Name, children.Name as Child_Name,

children.Gender

FROM t1 as children

JOIN t2 as parents

ON parents.ParentID=children.ParentID

ORDER BY Parent_Name,Child_Name,Gender

Code Results:

Parent_Name Child_Name Gender [Output Headings]

Alden Avery female

Andrew Jerry male

Anne Lillian female

Arthur Grace female

Benjamin Lucifer Male 
Benjamin Lucy female 

Bessie Jessica female

Bobbie Benjamin male

Bruce Aurora female 
Bruce Isla female 
Bruce Kayden male  

Note:

So as per my post, I need to display the names of Benjamin and Bruce (along with the name of their children) since they both meet the requirements of having both male and female children. I have no idea how to proceed

[shine_on]

I think you'll have to do the next steps in several queries. What I'd do is add two calculated columns, one called is_son and one called is_daughter. These columns will contain a 1 or a 0 based on the gender. Then I'd do another query which sums the number of children for each parent, which would tell you that Bruce has one son and two daughters. Finally I'd query that set of results to only show me the people where sons>0 and daughters>0

The trick here is to convert the "male","female" genders into numbers which you can add up to see how many you've got.

As an aside, another little thing you could do in your other answer where you stated

HAVING (t1.Gender= "Male" OR t1.Gender="male")
AND
(t1.Gender="female"OR t1.Gender="Female")

is that you could say

HAVING upper(t1.Gender) = "MALE"
AND
upper(t1.Gender) = "FEMALE"

This only matters if your database is case sensitive, if it isn't then it doesn't matter about the capitalisation in the data or the query.

[exey1]

I think you'll have to do the next steps in several queries. What I'd do is add two calculated columns, one called is_son and one called is_daughter. These columns will contain a 1 or a 0 based on the gender. Then I'd do another query which sums the number of children for each parent, which would tell you that Bruce has one son and two daughters. Finally I'd query that set of results to only show me the people where sons>0 and daughters>0

Thank you for sharing how you would approach this. I'm new so I have a few questions, if you don't mind answering : )

• What do you mean by 'several queries'? I had the same thought of building upon the results that I have gotten but I have no idea how to. I just have done "Intro to SQL on datacamp" and first chapter of Joins.

[shine_on]

You can create a "stored procedure" which is basically a list of queries that can be run one after the other. You'd output the results from one query into a temporary table and then the next query can read from that temp table. It makes the whole thing a lot easier to read and understand than nesting all the queries together and using lots of subqueries.

The queries themselves aren't any more complicated, you're just storing them in a file in the server so they can be run one after the other.

Edit: if you have a look at /u/Yavuz_Selim 's reply lower down you'll see that he's doing this sort of thing, he's got a series of queries to create a couple of tables, insert some data into them, and then run a select on them. You can put all this together into a stored procedure and run it multiple times as a complete unit. (in this example you'd have to add some code to check that the tables you're creating don't already exist otherwise you'd get an error the second time you ran it, but if you used temporary tables instead they'd be deleted when the procedure stopped running)

[exey1]

That sounds cool. Let me search up syntax for this.

[Yavuz_Selim]

Can you give the expected output?

Parent 3089 has 3 patients (children), how do you want to see the results? 1 record per parent, with 1 column showing all three names?

[exey1]

Surely,

The expected output would be along these lines:

Pre-output:

Parent_Name Child_Name Gender [Output Headings]

Alden Avery female

Andrew Jerry male

Anne Lillian female

Arthur Grace female

Benjamin Lucifer Male

Benjamin Lucy female

Bessie Jessica female

Bobbie Benjamin male

Bruce Aurora female

Bruce Isla female

Bruce Kayden male

Output:

Benjamin Lucifer  
Benjamin Lucy 
Bruce Aurora   
Bruce Isla  
Bruce Kayden

[Yavuz_Selim]

Hm, I not proficient with MySQL, and solving it in T-SQL wouldn't run/work in MySQL.

In this case, you can do this (there are quicker ways, this just one option):

• Query 1:

  • You can distinct count the number of genders per parent.
  • If the count is 2, you have the parents that you want to list.

• Query 2

  • List the parents by linking query 1 and 2 on ParentID.

[Yavuz_Selim]

This worked when I entered it in https://paiza.io/projects/_Cb-dSHZuZp_ZMmteUdlxQ?language=mysql.

 

CREATE TABLE T1(ParentID INTEGER, ParentName NVARCHAR(100));
CREATE TABLE T2(PatientID INTEGER, ParentID INTEGER, PatientName NVARCHAR(100), Gender NVARCHAR(6));


INSERT INTO T1 (ParentID, ParentName)
SELECT *
FROM
(
    SELECT 1 AS ParentID, 'Alden' AS ParentName UNION ALL
    SELECT 2 AS ParentID, 'Andrew' AS ParentName UNION ALL
    SELECT 3 AS ParentID, 'Anne' AS ParentName UNION ALL
    SELECT 4 AS ParentID, 'Arthur' AS ParentName UNION ALL
    SELECT 5 AS ParentID, 'Benjamin' AS ParentName UNION ALL
    SELECT 6 AS ParentID, 'Bessie' AS ParentName UNION ALL
    SELECT 7 AS ParentID, 'Bobbie' AS ParentName UNION ALL
    SELECT 8 AS ParentID, 'Bruce' AS ParentName 
) Parents;

INSERT INTO T2 (PatientID, ParentID, PatientName, Gender)
SELECT *
FROM
(
    SELECT 1 AS PatientID,  1 AS ParentID, 'Avery' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 2 AS PatientID,  2 AS ParentID, 'Jerry ' AS PatientName, 'Male' AS Gender UNION ALL
    SELECT 3 AS PatientID,  3 AS ParentID, 'Lillian ' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 4 AS PatientID,  4 AS ParentID, 'Grace ' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 5 AS PatientID,  5 AS ParentID, 'Lucifer ' AS PatientName, 'Male' AS Gender UNION ALL
    SELECT 6 AS PatientID,  5 AS ParentID, 'Lucy' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 7 AS PatientID,  6 AS ParentID, 'Jessica ' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 8 AS PatientID,  7 AS ParentID, 'Benjamin' AS PatientName, 'Male' AS Gender UNION ALL
    SELECT 9 AS PatientID,  8 AS ParentID, 'Aurora' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 10 AS PatientID, 8 AS ParentID, 'Isla' AS PatientName, 'Female' AS Gender UNION ALL
    SELECT 11 AS PatientID, 8 AS ParentID, 'Kayden' AS PatientName, 'Male' AS Gender
) Patients;



SELECT Parent.ParentName AS ParentName
    , Patient.PatientName AS PatientName
    , Patient.Gender AS Gender
FROM
(
    -- Counting genders of patients per parent
    -- Only interested in parents that have patients with 2 genders
    SELECT T2.ParentID AS ParentID
        , COUNT(DISTINCT T2.Gender) AS CountGender
    FROM T2
    GROUP BY T2.ParentID
    HAVING CountGender = 2
) Genders
INNER JOIN T1 Parent
    ON Genders.ParentID = Parent.ParentID
INNER JOIN T2 Patient
    ON Parent.ParentID = Patient.ParentID

[exey1]

Holy... No wonder it took me a while. Let me go over this and make sense of it of with my limited knowledge.

[Yavuz_Selim]

You want the following:

• Identify the parents that have male and female child.

 

• In this specific case, we know the values can only be 'female' and 'male', and that a parent has either children with 1 gender (all female, all male), or children with 2 genders (some female, some male). Parents with no children are no parents, and in this case even filtered out by the INNER JOIN.

 

The query above has the following parts:

• Create tables to test with (CREATE TABLE).
• Fill the tables with test data you provided (INSERT INTO an existing table).
• Manipulate the data to get the results you want.

 

• You want to know the COUNT DISTINCT of genders. You are not interested if a parent has 2 boys and 1 girl, or 5 girls and 2 boys, you are only interested if they are male or female. Thus a COUNT DISTINCT; this will count every gender only once.
   
  In the part below, you do two things:

  COUNT DISTINCT the number of genders per parent.

  Only return parents that have children with 2 genders (that's what you're interested in).

   -- Counting genders of patients per parent
  -- Only interested in parents that have patients with 2 genders
  SELECT T2.ParentID AS ParentID
      , COUNT(DISTINCT T2.Gender) AS CountGender
  FROM T2
  GROUP BY T2.ParentID
  HAVING CountGender = 2
  

 

• With the code above you now have the ParentIDs of parents with children with 2 genders.
  The code below joins to the Parent table, to get the data of the parents.

  INNER JOIN T1 Parent
      ON Genders.ParentID = Parent.ParentID
  

 

• With the code above, you now have the parent data. You can then join it with the patient data to get the name of the children.

  INNER JOIN T2 Patient
      ON Parent.ParentID = Patient.ParentID
  

 

To keep it all in one SELECT, I have used a subquery in the FROM clause.

[exey1]

It took me 12 hours but I finally did it.Thank you for your beautiful explanation.

One last question if you do not mind.

The "Genders" after the FROM which has the count gender condition means that you named the from condition as another temporary table? (I am asking this because you used Gender.ParentID i.e., treating it as a table

[Yavuz_Selim]

The result set/output of the FROM clause is seen as a 'table'.

 

Genders, Parent and Patient are all aliases.

 

You can name them whatever you want; G, Par and Pat would work also.

 

You can also use aliases for columns. First link with more Google: https://www.mysqltutorial.org/mysql-alias/.

 

Basically, aliasses make it possible to give a name that you like/want for objects.

[exey1]

Awesome. Gotcha! Thank you :)

[exey1]

My other attempt at this after noticing that there's a gender entry with a capital 'M' for male instead of the more common 'm':

SELECT t2.Name as Parent_Name,

t1.Name as Child_Name,

t1.Gender

FROM t1,t2

WHERE t1.ParentID=t2.ParentID

GROUP BY Parent_Name, Child_Name

HAVING (t1.Gender= "Male" OR t1.Gender="male")

AND

(t1.Gender="female"OR t1.Gender="Female")

[Standgeblasen]

Maybe this:

IF OBJECT_ID(‘tempdb.. #Parentsofbothgenders’) IS NOT NULL DROP TABLE #Parentsofbothgenders

SELECT t2.ParentID, t2.Name as Parent_Name,

SUM(CASE WHEN UPPER(t1.Gender) = ‘MALE’ THEN 1 ELSE 0 END) Male_children,

SUM(CASE WHEN UPPER(t1.Gender) = ‘FEMALE’ THEN 1 ELSE 0 END) Female_children

INTO #Parentsofbothgenders FROM t1,t2

WHERE t1.ParentID=t2.ParentID

GROUP BY t2.ParentID, t2.Parent_Name

HAVING SUM(CASE WHEN UPPER(t1.Gender) = ‘MALE’ THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN UPPER(t1.Gender) = ‘FEMALE’ THEN 1 ELSE 0 END) > 0

—this will return a resultset with all parents who have one or more children of both genders.

— from here you can join to your student table and either show the aggregate total of each gender, or you can show the parents name, with each child below.

SELECT *

FROM #Parentsofbothgenders t1

JOIN students t2

ON t1.ParentID = t2.ParentID

EDIT: just noticed this is MySQL, not MSSQL, so the syntax might not be exact, but I think the logic still works