CODEWITHSUNDEEP
sqlsql-server
Haris
Haris

Reputation: 788

Finding if something exists in a related table

I have a table employees and a table sales which are in a one-to-many relationship. Employees would contain fields such as name, email, id etc. and sales would include information such as country, number of sales, employeeID etc.

What I want to do now is find if for each and every employee if an entry in the sales table exists for a specific country.

My output should look something like

Employee.name, Employee.email, Sold to Canada

In "Sold to Canada" I simply want to put in a 1 or 0, depending on if for each employee there is an entry in sales where the condition for a specific country is met.

What I already tried out is this: 

    SELECT emp.name
      ,emp.email
      ,(SELECT COUNT(1) FROM sales sa JOIN employee emp ON emp.ID = sales.employeeID
      WHERE sales.country = 'Canada') as "Sold to Canada" 
FROM employee emp

This results in "Sold to Canada" being filled with a number much higher than 1, as I assume it returns the total number of employees that have made sales to Canada as opposed to the Information for each specific employee.

Upvotes: 0

Views: 77

Answers (4)

Alex
Alex

Reputation: 5157

There are many ways to skin a cat. One of the better ones is below:

SELECT emp.name, emp.email, ISNULL( Sold, 0 ) AS [Sold to Canada]
FROM employee AS emp
    LEFT JOIN 
            ( SELECT 1 AS Sold, employeeID
            FROM sales
            WHERE sales.country = 'Canada'
            GROUP BY employeeID ) AS CanadaSales
        ON emp.ID = CanadaSales.employeeID

Comments:

It is not a good idea to use correlated sub-queries in the SELECT part as this may result in inefficient query plans and therefore slow query performance.

Update: If you need to return results for multiple countries I would use PIVOT

SELECT emp.name, emp.email, ISNULL( Canada, 0 ) AS SoldCanada, ISNULL( Australia, 0 ) AS SoldAustralia /*, ISNULL( [another country], 0 ) AS [Sold Yet another country] */
FROM employee AS emp
    LEFT JOIN 
        ( SELECT *
        FROM
            ( 
                -- List of Country Employees
                SELECT 1 AS Sold, employeeID, country
                FROM sales
                GROUP BY employeeID, country ) AS EmpCountrySales
            PIVOT
            (
                -- For each country present return 1, else NULL
                MAX( Sold ) FOR country IN( [Canada], [Australia] /* , [another country] */ ) 
            ) AS pvt
        ) AS EmployeeSales

        ON emp.ID = EmployeeSales.employeeID

Comments: PIVOT function requires too many brackets.

Upvotes: 2

Shiblu
Shiblu

Reputation: 477

@Alex has already given a great answer. However, you can use this solution if you want to avoid sub-queries.

SELECT MIN(e.Name) AS EmployeeName
    , MIN(e.Email) AS Email
    , CASE WHEN MIN(EmployeeID) IS NOT NULL THEN 1 ELSE 0 END AS [Sold To Canada]
FROM Employee AS e
LEFT JOIN Sales AS s ON (s.EmployeeID = e.ID AND Country = 'Canada')
GROUP BY e.ID;

Upvotes: 3

dnoeth
dnoeth

Reputation: 60462

This is a direct translation into SQL using a Correlated Subquery :-)

SELECT emp.name
      ,emp.email
      ,CASE
         WHEN EXISTS(SELECT * FROM sales sa 
                     WHERE emp.ID = sa.employeeID
                       AND sa.country = 'Canada')
         THEN 1
         ELSE 0
       END as "Sold to Canada" 
FROM employee emp

Upvotes: 2

diiN__________
diiN__________

Reputation: 7656

You could do it with CASE:

SELECT emp.name,
    emp.email,
    (CASE WHEN emp.ID IN (SELECT sales.employeeID FROM sales WHERE sales.country = 'Canada') THEN 1 ELSE 0 END) AS [Sold to Canada]
FROM employee emp

Upvotes: 2

Related Questions