CODEWITHSUNDEEP
sql-server
Jun Rikson
Jun Rikson

Reputation: 1884

View from two tables with no key relations

I have category table below :

id  min     max
1   0       1000
2   1001    20000
3   20001   1000000000

and data table below :

id  weight
1   1000
2   500
3   2000
4   5000
5   20000
6   200
7   100
8   25000
9   18000
10  26000

I want to make a view that shows the data belong to which category like this :

data.id category.id
1       1
2       1
3       2
4       1
5       2
6       1
7       1
8       3
9       2
10      3

If there is duplicate category meet the criteria, just show the lowest category.id.

Update

Rules :

category.id = SELECT TOP(1) id FROM category WHERE @data.weight BETWEEN min AND max

Is that possible to do that just using view without stored procedure?

Upvotes: 1

Views: 39

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

This can be done with a join, or with a correlated subquery, or using apply as TcKs suggested in the comments. Here are the join and correlated subquery versions of the queries:

declare @category table (id int not null,min int not null,max int not null)
insert into @category(id,min,max) values
(1,0    ,1000       ),
(2,1001 ,20000      ),
(3,20001,1000000000 )

declare @data table (id int not null,weight int not null)
insert into @data(id,weight) values
(1 ,1000  ),
(2 ,500   ),
(3 ,2000  ),
(4 ,5000  ),
(5 ,20000 ),
(6 ,200   ),
(7 ,100   ),
(8 ,25000 ),
(9 ,18000 ),
(10,26000 )

select d.id,MIN(c.id) as category_id
from @data d
    inner join
    @category c
        on
            d.weight between c.min and c.max
group by d.id

select d.id,(select MIN(c.id) from @category c
            where c.min <= d.weight and c.max >= d.weight) as category_id
from @data d

I'm using table variables just to simplify the script. The choice between between and explicit comparisons is also arbitrary and either could be used in either query. If the ranges were semi-open (inclusive min, exclusive max is fairly common) then the explicit comparison variant would be required.

Results:

id          category_id
----------- -----------
1           1
2           1
3           2
4           2
5           2
6           1
7           1
8           3
9           2
10          3

Upvotes: 4

Related Questions