Help me with query, please

Transact-SQL
1

SQL1.png1152×648 36.7 KB

Basing on the picture I need to write the following query:

  • returning all employees older than 50 years and
  • participated in development of more than 10 products

The resultset should contain the following columns:

  • Name of the emloyee
  • Employee's age
  • The last product's date (basing on ProductEmployee.Date)

My solution is:

select e0.Name, e0.Age, tt.Name as TheLastProduct
from Employee e0
join (
select e.ID
from Employee e
join ProductEmployee pe on pe.EmployeeID = e.ID
join Product p on p.ID = pe.ProductID
where e.Age > 50
group by e.ID
having COUNT( p.ID) > 10
) e1 on e1.ID = e0.ID
cross apply
(
select top 1 p1.ID, p1.Name from Product p1
join ProductEmployee pe2 on p1.ID = pe2.ProductID
where pe2.EmployeeID = e1.ID order by pe2.Date desc
) tt

but I was told there might be a better solution. I wonder what the better solution is.

2

I would have done

SELECT ...
FROM	Employee AS E
WHERE	e.Age > 50
	AND 
	(
		SELECT	COUNT(*)
		FROM	ProductEmployee pe 
				on pe.EmployeeID = e.ID
			join Product p 
				on p.ID = pe.ProductID
	) > 10
CROSS APPLY ...

Difference between this, and yours, is that I only reference the Employee table once, so will probably be more efficient that yours (which re-references the Employee in the sub-query). It is also possible that mine will only COUNT the Products for rows where Employee > 50 - which would improve performance (assuming that there are some young people in the company!!)

If you have studied ROW_NUMBER ... OVER then you could use that to BOTH find the most-recent-product and also the COUNT of matches, in the same sub-query, which would be more efficient.

1 Like
3

I'd write the query the most natural way, and let SQL's optimizer decide the details of how to execute it. A correlated subquery can sometimes be inefficient. Also, you need to return the last product date, which a correlated subquery in a WHERE won't allow you to do.

SELECT e.Name, e.Age, p.Last_Product_Date
FROM Employee e
INNER JOIN (
    SELECT EmployeeID, MAX(Date) AS Last_Product_Date
    FROM Product
    GROUP BY EmployeeID
    HAVING COUNT(*) > 10
) AS p ON p.EmployeeID = e.ID
WHERE e.Age > 50