January 31, 2012 at 9:57 am
i have 3 tables
tbl_indicator
grp_nbr sect_nbr indicat
1 100 p
2 101 s
tbl_group
grp_id grp_nbr sect_nbr indicat
333 1 100 a
555 1 100 p
444 2 101 s
222 2 101 y
here (in tbl_group) grp_id is P.K
tbl_order
order_id grp_id
5000 333
5001 555
5002 555
5003 555
5004 444
5005 444
5006 222
here (in tbl_order) grp_id is F.K to grp_id in tbl_group
now the thing is that in table tbl_indiactor for one set of grp_nbr and sect_nbr there is an indicat, for the same set of grp_nbr and sect_nbr there is a correct indicat(555,1, 100, p) and a junk indicat(333, 1, 100, a) in table tbl_group.
but both these grp_id s(333, 555) are present in table tbl_orders
now i need to update tbl_order table in such a way that the junk grp_id s should replace with correct grp_id s
the output should like
tbl_orders
order_id grp_id
5000 555
5001 555
5002 555
5003 555
5004 444
5005 444
5006 444
can some one help me please.....
January 31, 2012 at 10:21 am
Is this what you are looking for?
declare @i table
(
grp_nbr varchar(1),
sect_nbr varchar(3),
indicat varchar(1)
)
declare @g table
(
grp_id varchar(3),
grp_nbr varchar(1),
sect_nbr varchar(3),
indicat varchar(1)
)
declare @o table
(
order_id varchar(4),
grp_id varchar(3)
)
insert into @i values
('1', '100', 'p'),
('2', '101', 's')
insert into @g values
('333', '1', '100', 'a'),
('555', '1', '100', 'p'),
('444', '2', '101', 's'),
('222', '2', '101', 'y')
insert into @o values
('5000', '333'),
('5001', '555'),
('5002', '555'),
('5003', '555'),
('5004', '444'),
('5005', '444'),
('5006', '222')
select o.order_id, g1.grp_id
from @o o
join @g g on g.grp_id = o.grp_id
join @i i on i.sect_nbr = g.sect_nbr
join @g g1 on g1.indicat = i.indicat
Edit: My left join didn't have to be a left join.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply