Select based on a lookup table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select based on a lookup table

Author

Topic

JacekK
Starting Member

14 Posts

Posted - 2010-10-05 : 16:05:15

I need to select records from a table based on a lookup table and certain conditions:

1. Get matching id and route_id in the lookup table.
2. Read corresponding row_num in the t1
3. Select only records that have larger row_num for that route_id.

Below are the sample to tables and desired output.
Here are 2 tables and a desired output table.

declare @t1 table
( id numeric(20), route_id varchar(20), row_num int)
insert into @t1 values (01,'012E', 1)
insert into @t1 values (02,'012E', 2)
insert into @t1 values (03,'012E', 3)
insert into @t1 values (04,'012E', 4)
insert into @t1 values (05,'012E', 5)
insert into @t1 values (06,'012E', 6)
insert into @t1 values (10,'035E', 1)
insert into @t1 values (11,'035E', 2)
insert into @t1 values (12,'035E', 3)
insert into @t1 values (13,'035E', 4)
insert into @t1 values (14,'035E', 5)
insert into @t1 values (16,'069E', 1)
insert into @t1 values (17,'069E', 2)
insert into @t1 values (18,'069E', 3)
insert into @t1 values (19,'069E', 4)

declare @t_lookup table
( id numeric(20), route_id varchar(20))
insert into @t_lookup values (04,'012E')
insert into @t_lookup values (13,'035E')
insert into @t_lookup values (18,'069E')

declare @t_results table
( id numeric(20), route_id varchar(20), row_num int)
insert into @t_results values (04,'012E', 4)
insert into @t_results values (05,'012E', 5)
insert into @t_results values (06,'012E', 6)
insert into @t_results values (13,'035E', 4)
insert into @t_results values (14,'035E', 5)
insert into @t_results values (18,'069E', 3)
insert into @t_results values (19,'069E', 4)

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-10-05 : 23:17:11

Try this ...

;with temp as
(select t2.*,t1.row_num from @t_lookup t2 join @t1 t1 on t2.id=t1.id and t2.route_id=t1.route_id)
select t1.* from temp te join @t1 t1 on t1.route_id=te.route_id and t1.row_num >=te.row_num

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-06 : 02:44:43

select t1.* from @t1 t1
inner join @t_lookup l on t1.route_id = l.route_id and t1.id>=l.id
select * from @t_results

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources