CODEWITHSUNDEEP
t-sql
Johann
Johann

Reputation: 29877

T-SQL: finding rows in a different table without joins

I have two tables, I'll call TableA and TableB

TableA:

StartNumber EndNumber Country

1           10        USA
11          20        USA
21          30        Canada
31          40        France
41          50        France
51          60        Germany

TableB:

SomeNumber
5
15
55
22
35
46
49

For each number in TableB, I want to find the corresponding row in TableA where the number is between the StartNumber and EndNumber and return the name of the country. I then want to group these results on the country column and return the number of times each country appears. So the results would look like this:

Country Occurrences
USA     2
Germany 1
Canada  1
France  3

Not sure how to do this.

Upvotes: 1

Views: 113

Answers (2)

dani herrera
dani herrera

Reputation: 51715

Here the query.

Select A.Country, count(*) as Occurrences
from 
 tableA A
    inner join
 tableB B
    on B.someNumber between a.startnumber and b.endnumber
group by A.country

Upvotes: 2

domager
domager

Reputation: 769

This should do the trick (but does use a join):

declare @TableA table (StartNumber int, EndNumber int, Country varchar(16));
insert into @TableA (StartNumber, EndNumber, Country)
select 1, 10, 'USA' union
select 11, 20, 'USA' union
select 21, 30, 'Canada' union
select 31, 40, 'France' union
select 41, 50, 'France' union
select 51, 60, 'Germany';

declare @TableB table (SomeNumber int);
insert into @TableB (SomeNumber)
select 5 union
select 15 union
select 55 union
select 22 union
select 35 union
select 46 union
select 49;

select
    a.Country, count(*) Occurrences 
from
    @TableA a inner join
    @TableB b on b.SomeNumber between a.StartNumber and a.EndNumber
group by
    a.Country;

Upvotes: 0

Related Questions