SQL Return only where more than one join.

Question

Not sure how to ask this as I'm a bit of a database noob,

What I want to do is the following.

table tb_Company
table tb_Division

I want to return companies that have more than one division and I don't know how to do the where clause.

SELECT   dbo.tb_Company.CompanyID, dbo.tb_Company.CompanyName, 
dbo.tb_Division.DivisionName FROM dbo.tb_Company INNER JOIN dbo.tb_Division ON 
dbo.tb_Company.CompanyID = dbo.tb_Division.DivisionCompanyID

Any help or links much appreciated.

Answer 1

You'll need another JOIN where you only return companies having more than one division by using a GROUP BYand a HAVINGclause.

You can read up on grouping here

Groups a selected set of rows into a set of summary rows by the values of one or morecolumns or expressions. One row is returned for each group. Aggregate functions in the SELECT clause list provide information about each group instead of individual rows.

SELECT   dbo.tb_Company.CompanyID
        , dbo.tb_Company.CompanyName
        , dbo.tb_Division.DivisionName 
FROM    dbo.tb_Company 
        INNER JOIN dbo.tb_Division ON dbo.tb_Company.CompanyID = dbo.tb_Division.DivisionCompanyID
        INNER JOIN (
          SELECT  DivisionCompanyID
          FROM    dbo.tb_Division
          GROUP BY
                  DivisionCompanyID
          HAVING  COUNT(*) > 1
        ) d ON d.DivisionCompanyID = dbo.tb_Company.CompanyID

Answer 2

How about?

WITH COUNTED AS
(
  SELECT C.CompanyID, C.CompanyName, D.DivisionName,
         COUNT() OVER(PARTITION BY C.CompanyID) AS Cnt
  FROM dbo.tb_Company C
  INNER JOIN dbo.tb_Division D ON C.CompanyID = D.DivisionCompanyID
)
SELECT *
FROM COUNTED
WHERE Cnt > 1

With the other solutions (that join onto Division table twice), a single company/division can be returned under a heavy insert load.

If a row is inserted into the Division table between the time the first join occurs and the time the second join (with the group by/having) is evaluated, the first Division join will return a single row. However, the second one will return a count of 2.

Answer 3

another alternative...

SELECT c.CompanyId, c.CompanyName, d.DivisionName
FROM tbl_Company c
INNER JOIN tbl_Division d ON c.CompanyId=d.DivisionCompanyId
GROUP BY c.CompanyId, c.CompanyName, d.DivisionName
HAVING COUNT(*) > 1

Answer 4

How about...

SELECT dbo.tb_Company.CompanyID,
       dbo.tb_Company.CompanyName, 
    FROM dbo.tb_Company
    WHERE (SELECT COUNT(*)
               FROM dbo.tb_Division
               WHERE dbo.tb_Company.CompanyID =
                          dbo.tb_Division.DivisionCompanyID) > 1;