CODEWITHSUNDEEP
sql-server-2008select
HL8
HL8

Reputation: 1419

Return value from within a range in SQL Server 2008

I have two tables, one where it contains members & their cardnumbers, and outlet table which identifies the cardnumber ranges per outlet. I want to know which store the cardnumber the member has belongs to.

Members Table

MemberID    Cardnumber    FirstName   LastName
1           123456123     John        Doe
2           123456245     Sarah       Smith

Outlets Table

OutletID    OutletName      StartCardNumber     EndCardNumber
1           Balmain Store   123456100           123456200
2           Sydney Store    123456201           123456300

I can't think of a script which I can bring back the following information without having to create a temp table first. Is there an easier way?

CardNumber  FirstName LastName   OutletName
123456123   John      Doe        Balmain Store
123456245   Sarah     Smith      Sydney Store

Upvotes: 0

Views: 124

Answers (1)

ErikE
ErikE

Reputation: 50272

It's very simple. You join on a range using inequalities in addition to equalities.

SELECT
   M.CardNumber,
   M.FirstName,
   M.LastName,
   O.OutletName
FROM
   dbo.Members M
   INNER JOIN dbo.Outlets O
      ON M.CardNumber >= O.StartCardNumber
      AND M.CardNumber <= O.EndCardNumber

This is the same as M.CardNumber BETWEEN O.StartCardNumber AND O.EndCardNumber but I wanted to draw out the inclusive endpoints of your scheme using >= and <=. BETWEEN is not always suitable because very often the end value is exclusive requiring <, but not in this case.

Try this out right now online!

Upvotes: 2

Related Questions