CODEWITHSUNDEEP
mysql
Kenta56
Kenta56

Reputation: 11

Find people who have the same day of birth

I'm looking for a way to create a query that gives me the list of people who were born on the same day (the data is in two different tables). All ideas are welcome. ( I use MySQL)

SQL code :

https://pastebin.com/StNggaYg

CREATE TABLE Professeurs(
        ID_Professeur        Int  Auto_increment  NOT NULL ,
        Nom_Professeur       Varchar (50) NOT NULL ,
        Prenom_Professeur    Varchar (50) NOT NULL ,
        Ville_de_naissance_P Varchar (50) NOT NULL ,
        Date_de_naissance_P  Date NOT NULL ,
        Professeur_Principal Bool NOT NULL
    ,CONSTRAINT Professeurs_PK PRIMARY KEY (ID_Professeur)
)ENGINE=InnoDB;
 
 
#------------------------------------------------------------
# Table: Classes
#------------------------------------------------------------
 
CREATE TABLE Classes(
        ID_Classes    Int  Auto_increment  NOT NULL ,
        Lettre_classe Char (5) NOT NULL ,
        ID_Professeur Int NOT NULL
    ,CONSTRAINT Classes_PK PRIMARY KEY (ID_Classes)
 
    ,CONSTRAINT Classes_Professeurs_FK FOREIGN KEY (ID_Professeur) REFERENCES Professeurs(ID_Professeur)
)ENGINE=InnoDB;
 
 
#------------------------------------------------------------
# Table: Elèves
#------------------------------------------------------------
 
CREATE TABLE Eleves(
        ID_Eleve             Int  Auto_increment  NOT NULL ,
        Nom_eleve            Varchar (50) NOT NULL ,
        Prenom_eleve         Varchar (50) NOT NULL ,
        Ville_de_naissance_E Varchar (50) NOT NULL ,
        Date_de_naissance_E  Date NOT NULL ,
        ID_Classes           Int NOT NULL
    ,CONSTRAINT Eleves_PK PRIMARY KEY (ID_Eleve)
 
    ,CONSTRAINT Eleves_Classes_FK FOREIGN KEY (ID_Classes) REFERENCES Classes(ID_Classes)
)ENGINE=InnoDB;

The column : Date_de_naissance_E Date NOT NULL , Date_de_naissance_P Date NOT NULL ,

Upvotes: 1

Views: 846

Answers (2)

DRapp
DRapp

Reputation: 48179

First, you would have to get the results of BOTH tables dates via a UNION to find what dates have more than one instance. Its possible to have two teachers with same date, two students with same date, OR a teacher AND student have same date. Once that is done, then you can join back to the respective original source to grab the names.

To simplify the query from rewriting it twice nested within itself, I will use CTE (common table expression). It allows you to write a query that will be used multiple times allowing simple alias name to be used in a subsequent query.

Below is the CTE via "With AllPeople as". This select is the query used in the second half. Notice I am getting ID, name, birth date and also a type "P" as Professeur and "E" as Eleves for the person type. First time through I am getting a count where a given birthdate occurs more than once. THEN, re-join to the same AllPeople again that had that date found as a common birth date.

with AllPeople as
(   select
            'P' personType,
            p.ID_Professeur personID,
            p.Nom_Professeur personName,
            Date_Format( p.Date_de_naissance_P, "%M %d" ) commonDate
        from 
            Professeurs p
    union all
    select     
            'E' personType,
            e.ID_Eleve personID,
            e.nom_eleve personName,
            Date_Format( e.Date_de_naissance_E, "%M %d" ) commonDate
        from
            Eleves e 
)

select
        who.PersonType,
        who.PersonID,
        who.PersonName,
        BornOnSameDay.commonDate
    from
        (select
                commonDate
            from
                AllPeople
            group by
                commonDate
            having
                count(*) > 1 ) BornOnSameDay
        JOIN 
            AllPeople Who
            on BornOnSameDay.commonDate = Who.commonDate
    order by
        BornOnSameDay.commonDate,
        who.PersonName

Now, you also provided a class table of which teachers were teaching what class, and if a student was in a particular class. If your intention was to find out which classes has a student with the same birthdate as the professor teaching it, that would be a different query.

Please advise on which you meant. The one I have provided is ANY teacher having a same birthday as ANY other teacher OR student. And likewise could be two students having the same birth date.

Upvotes: 1

IVO GELOV
IVO GELOV

Reputation: 14289

You can use the GROUP_CONCAT function:

SELECT date_format(birthdate,"%d %b") AS birthday, GROUP_CONCAT(person_name) AS people 
FROM persons
GROUP BY MONTH(birthdate), DATE(birthdate)

Upvotes: 0

Related Questions