Where clause in multiple joins in mysql query

Question

I have following tables:- - Employees - Orders - OrderDetails

Employees
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| EmployeeID      | int          | NO   | PRI | NULL    |       |
| LastName        | varchar(20)  | YES  |     | NULL    |       |
| FirstName       | varchar(10)  | YES  |     | NULL    |       |
| Title           | varchar(30)  | YES  |     | NULL    |       |
| TitleOfCourtesy | varchar(25)  | YES  |     | NULL    |       |
| BirthDate       | datetime     | YES  |     | NULL    |       |
| HireDate        | datetime     | YES  |     | NULL    |       |
| Address         | varchar(60)  | YES  |     | NULL    |       |
| City            | varchar(15)  | YES  |     | NULL    |       |
| Region          | varchar(15)  | YES  |     | NULL    |       |
| PostalCode      | varchar(10)  | YES  |     | NULL    |       |
| Country         | varchar(15)  | YES  |     | NULL    |       |
| HomePhone       | varchar(24)  | YES  |     | NULL    |       |
| Extension       | varchar(4)   | YES  |     | NULL    |       |
| Notes           | mediumtext   | YES  |     | NULL    |       |
| ReportsTo       | int          | YES  |     | NULL    |       |
| PhotoPath       | varchar(255) | YES  |     | NULL    |       |
| Salary          | float        | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
Orders
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| OrderID        | int           | NO   | PRI | NULL    |       |
| CustomerID     | varchar(5)    | YES  |     | NULL    |       |
| EmployeeID     | int           | YES  |     | NULL    |       |
| OrderDate      | datetime      | YES  |     | NULL    |       |
| RequiredDate   | datetime      | YES  |     | NULL    |       |
| ShippedDate    | datetime      | YES  |     | NULL    |       |
| ShipVia        | int           | YES  |     | NULL    |       |
| Freight        | decimal(10,4) | YES  |     | NULL    |       |
| ShipName       | varchar(40)   | YES  |     | NULL    |       |
| ShipAddress    | varchar(60)   | YES  |     | NULL    |       |
| ShipCity       | varchar(15)   | YES  |     | NULL    |       |
| ShipRegion     | varchar(15)   | YES  |     | NULL    |       |
| ShipPostalCode | varchar(10)   | YES  |     | NULL    |       |
| ShipCountry    | varchar(15)   | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
OrdreDetails
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| OrderID   | int           | YES  | MUL | NULL    |       |
| ProductID | int           | YES  | MUL | NULL    |       |
| UnitPrice | decimal(10,4) | YES  |     | NULL    |       |
| Quantity  | smallint      | YES  |     | NULL    |       |
| Discount  | double        | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

My question is Give the identiﬁer, name, and total sales of employees, ordered by the employee identiﬁer for employees who have sold more than 70 diﬀerent products

I wrote this query

select t1.EmployeeID as Identifier,
       concat(t1.FirstName, ' ', t1.LastName) as Name,
       (select count(*) from orders t4 where t4.EmployeeID = identifier) as Total_Sales,
       count(distinct(t3.ProductID)) as Total_unique_products
  from Employees t1
 inner join Orders t2
    on t1.EmployeeID = t2.EmployeeID
 inner join orderdetails t3
    on t2.OrderID = t3.OrderID
 group by t1.EmployeeID
 order by t1.EmployeeID;

i also want to show only those where where Total_unique_products is greator than 70; how do i do it?

Hints (because I'm too lazy to answer it) GROUP BY, HAVING, and maybe Total_Sales doesn't need to be a subquery. — danblack
– danblack, Commented May 22, 2020 at 7:55

Barbaros Özhan · Accepted Answer · 2020-05-22 08:31:30Z

1

You need a having clause, concat(t1.FirstName, ' ', t1.LastName) column added to group by clause, and reformat count(distinct...) expression ( inner parentheses are redundant ).

A left join would be a better alternative, since there may exist non-matching records.

A correlated subquery is not needed. So convert yours to this one :

select e.EmployeeID as Identifier,
       concat(e.FirstName, ' ', e.LastName) as Name,
       count(o.ID) as Total_Sales,
       count(distinct od.ProductID) as Total_unique_products
  from Employees e
  left join Orders o
    on e.EmployeeID = o.EmployeeID
  left join orderdetails od
    on o.OrderID = od.OrderID
 group by e.EmployeeID, concat(e.FirstName, ' ', e.LastName)
having count(distinct od.ProductID) > 70
 order by e.EmployeeID;

edited May 22, 2020 at 8:31

answered May 22, 2020 at 8:07

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pratyaksh Saini Over a year ago

Thats awesome, one more thing, why did you grouped by Name also, isn't grouping by ID enough?

Barbaros Özhan Over a year ago

Nice. Normally any non-aggregated column should be added to the GROUP BY list for most of the DBMS. Mysql gives an exception unless ONLY_FULL_GROUP_BY is enabled @PratyakshSaini .

Grace · Accepted Answer · 2020-05-22 08:19:24Z

0

Mainly use use where to filter for the specific employee, also use having to filter records whose Total_unique_products is greator than 70

select 
    t1.EmployeeID as Identifier, 
    concat(t1.FirstName, ' ', t1.LastName) as Name, 
    count(t2.OrderID) as Total_Sales, 
    count(distinct t3.ProductID) as Total_unique_products 
from Employees t1 
inner join Orders t2 on t1.EmployeeID = t2.EmployeeID 
inner join orderdetails t3 on t2.OrderID = t3.OrderID 
where t2.EmployeeID = identifier
group by t1.EmployeeID 
having count(distinct(t3.ProductID)) >70
order by t1.EmployeeID;

answered May 22, 2020 at 8:19

Grace

8981 gold badge12 silver badges21 bronze badges

Collectives™ on Stack Overflow

Where clause in multiple joins in mysql query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related