Assign categories to records from one table by matching on the largest number in the second table which is less than or equal to a value in the first table

I have two tables, each with a numeric value and a text value.  I want to use the numeric value in each record in the first table to select the record in the second table whose numeric value is the largest value less than or equal to the value in the first table, and join the two records.  An example might be to categorize people into age groups:

Table 1

Fred  50
Ralph  32
Sarah  43

Table 2

Twenties  20
Thirties  30
Forties  40
Fifties  50
Sixties  60

Result

Fred  50  Fifties
Ralph  32  Thirties
Sarah  43  Forties

I don't even know what to call such a join, nor if it is possible with a query or would have to be done in SQL.  Seems like this would be a fairly common problem for sorting things into groups, but I haven't been able to word an online search properly to turn up postings of similar problems.


ADDENDUM:  Thank you for your responses.  That was a bad example, I apologize.  The second table will contain starting points for each range, but in the "real life" problem I am trying to solve, they will not all be multiples of 10, they could be any integer.
jeoincAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
You inner join on Table2.[Age] = (Table1.[Age] - (Table1.[Age] mod 10))
0
PortletPaulEE Topic AdvisorCommented:
This is not a common method of joining in my view. If you wanted to design a more generic solution you would define ranges in the second table, e.g.

dataTable
name, val
Fred,  50
Ralph,  32
Sarah,  43

rangeTable
rangename, low, high
Twenties,  20, 30
Thirties,  30, 40
Forties,  40, 50
Fifties,  50, 60
Sixties,  60, 9999

select  d.name, d.val, r.rangename
from dataTable d
inner join rangeTable r on d.val >= d.low and d.val < r.high

select r.low, r.rangename, count(d.val)
from dataTable d
inner join rangeTable r on d.val >= d.low and d.val < r.high
group by r.low, r.rangename
order by r.low
0
Gustav BrockCIOCommented:
You can simply divide the age by 10:

Select 
    Table1.Name, 
    Table1.Age, 
    Table2.DecadeName
From 
    Table1,
    Table2
Where
    Table1.Age \ 10 = Table2.Decade

Open in new window

Notice the backslash to perfrom integer division.

That will return your requested result.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

aikimarkCommented:
@Gustav

Won't the Table2 values also need integer division?
0
Gustav BrockCIOCommented:
Yes, you are right - or this, which I originally had in mind:

Where
    (Table1.Age \ 10) * 10 = Table2.Decade

Open in new window

0
jeoincAuthor Commented:
Hi, all, and thank you.  Please see Addendum to the question, the way I originally presented the problem was misleading and I have clarified it.
0
aikimarkCommented:
both my and Gustav's solutions should work for any values of 10.
0
aikimarkCommented:
If your tests prove otherwise, please post a more representative data sample
0
Gustav BrockCIOCommented:
Then it could be:

Select 
    Table1.Name, 
    Table1.Age, 
        (Select DecadeName From Table2 As T1
        Where T1.Decade = 
            (Select Top 1 Decade From Table2 As T2
            Where T2.Decade <= Table1.Age
            Order By T2.Decade Desc)) As DecadeName
From 
    Table1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Re your addendum:

This was the point i attempted to make in my original response. I believe you need to define ranges through the second table,  not rely on just a start point of a range.

It can't be assumed that each range is of equal size (such as 10 years) or that they are mutually exclusive. Add another column to the second table and provide both the lower and upper boundary points of each range.
0
jeoincAuthor Commented:
I did not get to look at this yesterday, just getting back to it today.

I do not know SQL beyond the very basics so I am having to go through Gustav's proposal very carefully to try and understand it, but testing with it in my database, it looks like it is exactly what I am looking for.

I still want to review it a little further before marking it as the answer, I will try to finish understanding it tomorrow and get it so marked once I am sure.

Thank you very much!
0
jeoincAuthor Commented:
Thank you so much to everyone who replied.

The second solution from Gustav was exactly what I was looking for.  This has also helped me grasp a few more aspects of SQL (this is being done in Access but I almost exclusively use the graphical query builder).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.