asked on

SQL - Join 2 Tables Based on Ranges

I have two tables that I need to join together. One table has a list of letter ranges and the employee assigned to that range. The other table is a list of manufacturer names. I need to be able to join the two tables so that if the manufacturer's name is in one of the ranges, it will list the person whom is responsible for that manufacturer.

Example:

Table 1: Range_Assignment
The [Range] column is calculated as [Range_Start] + ' - ' + [Range_End]

Table 2: Manufacturers

Desired Outcome

How can I do this with a pure SQL statement? Is it possible without using a CTE or any other temporary tables? Also, I want to be able to adjust the ranges as needed, so I don't want to hard-code the range into the statement.

Pawan Kumar

Hi,
Have you tried any t-SQL for this?

Brian Pringle

ASKER

That is what I am trying to figure out... I need to find a SQL query that will accomplish this.

Dustin Wade

Amateurish in SQL myself, but would this work?

SELECT Manufacturers_Name FROM Manufacturers
JOIN Range_Assignment
ON Manufacturers.Manufacturer_Name
AND Manufacturers.Manufacturer_Name BETWEEN Range_Assignment.range_start AND Range_Assignment.range_end;

EDIT: Got my select wrong I think

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Pringle

ASKER

@Pawan Kumar Khowal, this is working for most of the manufacturers, but I overly simplified the list. The "M" manufacturers actually have three different people. The ranges are MA-MC, MD-MED, and MEE-MZ.

Your first statement sets the variables to "1" because it looks at the max (Z) and min (0) values in the ranges and their lengths are both 1. How can I account for the varying range lengths?

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Pringle

ASKER

I had to modify it a little bit, but this is what is working...

select m.Manufacturer_Name, ra.Employee
from Manufacturers m
inner join Range_Assignment ra on
	left(upper(m.Manufacturer_Name), len(ra.Range_Start)) >= upper(ra.Range_Start)
	and left(upper(m.Manufacturer_Name), len(ra.Range_End)) <= upper(ra.Range_End)
order by 1

Open in new window

Brian Pringle

ASKER

Thanks, guys! I appreciate the help!!!