CODEWITHSUNDEEP
sql-servert-sql
Lucas Araujo
Lucas Araujo

Reputation: 143

How to get columns that are referenced in another table a certain number of times? in SQL Server

So I have two hypothetical tables

I know that group is a reserved word but it's just for the sake of the example

What I want to get is the countries with 3 or more groups without the use of another referential table.

I have tried the following

select *
from Country c
where CountryCode in (select g.CountryCode
                      from Group g
                      where g.CountryCode=c.CountryCode
                      group by g.CountryCode
                      having count(*) > 3)

But I get no results given I have the following data in my Groups table:

|GroupId|GroupName|CountryCode|
|   1   |  'asd'  |    USA    |
|   4   |  'fgh'  |    USA    |
|   3   |  'jkl'  |    USA    |
|   4   |  'zxc'  |    ARG    |

The result I want is:

|CountryCode| CountryName|
|    USA    |UnitedStates|

because there 3 groups with the CountryCode = USA

Upvotes: 0

Views: 44

Answers (3)

neer
neer

Reputation: 4092

You are almost true without where clause But Answer of @Mahedi Sabuj is better for performance

 SELECT *
 FROM Country c
 WHERE CountryCode IN 
                (
                  SELECT g.CountryCode
                  FROM Group g
                  GROUP BY g.CountryCode
                  HAVING COUNT(*) > 3
                )

Upvotes: 1

Mahedi Sabuj
Mahedi Sabuj

Reputation: 2944

Get those country code by using GROUP BY and Having then Join the result with Country table, you will get your expected result.

select C.*
from 
(
   select g.CountryCode
   from Group g
   group by g.CountryCode
   having count(*) >= 3
) CC
INNER JOIN Country C ON C.CountryCode = CC.CountryCode

UPDATE Without JOIN

select C.*
from Country C
WHERE C.CountryCode IN
(
   select g.CountryCode
   from Group g
   group by g.CountryCode
   having count(*) >= 3
)

Upvotes: 1

M.Ali
M.Ali

Reputation: 69564

Select c.CountryCode,  c.CountryName  
FROM [Country] c
INNER JOIN [Group] g ON c.CountryCode = g.CountryCode
GROUP BY c.CountryCode,  c.CountryName 
HAVING COUNT(DISTINCT GroupName) >= 3

Upvotes: 0

Related Questions