In TSQL, how do we select a value from a table with no relationship to the other.

I'm trying to select what rank an ID has, by looking up their score in another table. I was going to join and then use a 'where Score between From and TO' but, I had no luck joining.

Table_A

Table_B

Expected query result:

I started with

Select ID From Table_A
Inner Join 

I got lost here because there is no relationship

I could get the result using a scalar function, but in terms of performance, where Table_A has over 500k rows, it seemed a little sluggish because Table_B not only holds rank, but has other columns I need for the query.

For example:

Table_B

Basically, if I can be shown how to query at least the rank, I can get the other columns as well.

CodePudding user response：

You can use BETWEEN as the JOIN condition, like so:

CREATE TABLE #Table_A
(
ID VARCHAR(255),
Score INT
)
;

INSERT #Table_A ([ID], [Score]) VALUES
('A', 67),
('B', 569),
('C', 123);

CREATE TABLE #Table_B
(
    [From] INT,
    [To] INT,
    [Rank] VARCHAR(255)
)

INSERT #Table_B ([From], [To], [Rank]) VALUES
( 1, 99, 'Top100'), (100, 499, 'Top500'), (500, 999, 'Top1000');

-- Query here

SELECT A.[ID], B.[Rank]
FROM #Table_A A
INNER JOIN #Table_B B ON A.Score BETWEEN B.[From] AND B.[To]

CodePudding user response：

You could also use CROSS APPLY to get your favorite result

;WITH Table_A AS 
(
    SELECT 'A' ID,  67 Score
    Union
    SELECT 'B' ID,  569 Score
    Union
    SELECT 'C' ID,  123 Score
), 
Table_B AS 
(
    SELECT 1 [From], 99 [To], 'Top100' [Rank]
    Union
    SELECT 100 [From], 499 [To], 'Top500' [Rank]
    Union
    SELECT 500 [From], 999 [To], 'Top1000' [Rank]
)

SELECT Table_A.[ID], Table_B.[Rank]
FROM Table_A 
    CROSS APPLY Table_B  
WHERE Table_A.Score 
    BETWEEN Table_B.[From] AND Table_B.[To]
GO