CODEWITHSUNDEEP
mysqldatabase
Aniket Kumar
Aniket Kumar

Reputation: 13

How to find DISTINCT Combinations?

There are 3 tables :

CREATE TABLE Employee(
  EID mediumint PRIMARY KEY AUTO_INCREMENT,
  EName varchar(100) DEFAULT NULL,
  EPincode mediumint unsigned NULL
);

EID is the Employee-Id of each Employee which is unique;

CREATE TABLE Project(
  PID mediumint PRIMARY KEY AUTO_INCREMENT,
  PName varchar(30) DEFAULT NULL,
  MID mediumint UNIQUE NOT NULL,
  FOREIGN KEY (MID) REFERENCES Employee(EID)
);

MID is the Manager-Id of an Employee who is heading the Project.

CREATE TABLE Works_On(
  EID mediumint NOT NULL,
  PID mediumint NOT NULL,
  FOREIGN KEY (EID) REFERENCES Employee(EID),
  FOREIGN KEY (PID) REFERENCES Project(PID)
);

This table consist of all the Employees and Their Associated Project.

An Employee can work on multiple Projects. A Project can be done by multiple Employees. Each Project has a Manager. A Manager can have one or more employees under him.

I want to find a table consisting of Employee Name, Project Name and Manager Name. Please help.

This is the query I have written so far but its not working :

SELECT DISTINCT A.EName AS "Employee Name"
               ,A.PName AS "Project Name"
               ,B.EName AS "Manager Name" 
FROM (SELECT P.PName
            ,E.EName 
      from Works_On W 
      NATURAL JOIN Project P 
      NATURAL JOIN Employee E) A,
(SELECT E.EName 
 from Project P 
 INNER JOIN Employee E ON P.MID = E.EID) B

Upvotes: 0

Views: 100

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

select A.Employee_name,
       A.project_name,
       B.manager_name  from
      (
        SELECT
        P.PName AS project_name,
        E.EName AS Employee_name,
        E.EID,P.PID
        FROM Works_On W
        INNER JOIN Project P ON W.PID = P.PID
        INNER JOIN Employee E ON W.EID = E.EID

        ) as A           

       left join
       (

        SELECT
        E.EName AS manager_name,
        E.EID,P.PID
        FROM Project P
        INNER JOIN Employee E ON P.MID = E.EID

        ) B on A.PID=B.PID

Upvotes: 0

Aniket Kumar
Aniket Kumar

Reputation: 13

SELECT P.PName AS ProjectName,
       M.EName AS ManagerName, 
       E.EName AS EmployeeName 
FROM Project P 
     LEFT JOIN Employee M ON (P.MID=M.EID) 
     LEFT JOIN Works_On WO on (P.PID=WO.PID) 
     LEFT JOIN Employee E on (E.EID=WO.EID);

It worked.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13517

You can try below query - 

SELECT
    E.EName,
    P.PName,
    M.EName
FROM Employee E 
    INNER JOIN Employee M ON M.EID = E.EID
    INNER JOIN Project P ON E.EID = P.MID
    INNER JOIN Works_On W ON W.EID = E.EID AND W.PID = P.PID

This might help you.

Upvotes: 0

Related Questions