Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation—Intermediate Level

Answer: Using CROSS JOIN

Explore how to use CROSS JOIN in SQL to generate all possible row combinations between tables and solve complex data retrieval tasks. Understand key concepts such as joins, aggregate functions, aliases, and grouping while learning alternative query methods to count sales per employee by product category.

We'll cover the following...

Solution

The solution is given below:

MySQL
-- The query to find employees with sales in each category
SELECT     E.EName AS 'Employee Name',
           PC.CategoryName AS 'Category Name',
           COUNT(S.SalesID) AS 'Sales Count'
FROM       Employees E
CROSS JOIN ProductCategories PC
LEFT JOIN  Sales S ON E.EID = S.EID AND S.CategoryID = PC.CategoryID
GROUP BY   E.EName, PC.CategoryName;

Code explanation

The explanation of the solution code is given below:

  • Lines 2–4: The SELECT statement selects the columns EName, CategoryName, and count of sales made using the aggregate function with the SalesID column. We use AS to set an alias for the columns and tables.

  • Line 5: The data is retrieved from the Employees table.

  • Line 6: The CROSS JOIN is performed between Employees and ProductCategories.

  • Line 7: The LEFT JOIN is performed with Sales, Employees, and ProductCategories on the columns EID and CategoryID. ...