CODEWITHSUNDEEP
sqlsql-servercountsubquery
James Wierzba
James Wierzba

Reputation: 17498

Use result of select column subquery in the where clause of the outer query?

I've got a table User and Company. The User records are a child of the Company records i.e. the User table has a column parent_company_id indicating which company the user is a part of.

I want to select company records, along with a count of how many of the companies associated user records have a first name of james, only where that count is > 0.

This is my first thought:

SELECT
  c.name,
  (SELECT
    COUNT(*)
  FROM [user] u
  WHERE first_name = 'James'
  AND u.parent_company_id = company_id)
  AS james_count
FROM company c
WHERE james_count > 0;

This does not compile because the outer query does not know of the james_count column alias. Why not?

This works, but won't this be slower because it will run two subqueries?

SELECT
  c.name,
  (SELECT
    COUNT(*)
  FROM [user] u
  WHERE first_name = 'James'
  AND u.parent_company_id = company_id)
  AS james_count
FROM company c
WHERE (SELECT
  COUNT(*)
FROM [user] u
WHERE first_name = 'James'
AND u.parent_company_id = company_id)
> 0;

Upvotes: 1

Views: 4360

Answers (2)

Jay
Jay

Reputation: 2200

You can use a CTE for this. First select company ID's against the count of first names with James. Then join that table with the Company table.

WITH   James (parent_company_id, james_count)
AS     (SELECT   parent_company_id,
                 COUNT(*)
        FROM     [User]
        WHERE    first_name = 'James'
        GROUP BY parent_company_id)
SELECT c.*,
       j.james_count AS no_of_companies_with_user_james
FROM   [Company] AS c
       INNER JOIN
       [James] AS j
       ON c.company_id = j.parent_company_id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

There are many ways to do this. But one is to use cross apply:

select c.name, j.james_count  
from company c cross apply
     (select count(*) as james_count
      from [user] u
      where first_name = 'James' and u.parent_company_id = company_id
     ) j
where james_count > 0;

The more natural way is to simply use a join and group by:

select c.name, count(*) as james_count
from company c join
     [user] u
     on u.parent_company_id = c.company_id and u.first_name = 'James'
group by c.name;

Upvotes: 3

Related Questions