September 20, 2010 at 9:02 pm
I have two tables
A(BookNo)
B(BookNoFrom,BookNoTo,ChangeBookNo)
Create table A(BookNo char(4))
Create table B(BookNoFrom char(4),BookNoTo char(4),ChangeBookNo char(4))
and some data...
Insert into A(BookNo) values('1234')
Insert into A(BookNo) values('2234')
Insert into A(BookNo) values('3234')
Insert into B(BookNoFrom,BookNoTo,ChangeBookNo) values('1230','1235','1111')
Insert into B(BookNoFrom,BookNoTo,ChangeBookNo) values('1231','1236','2222')
Insert into B(BookNoFrom,BookNoTo,ChangeBookNo) values('1232','1237','3333')
Insert into B(BookNoFrom,BookNoTo,ChangeBookNo) values('5231','5236','4444')
I want to write SQL such as:
- If A.BookNo Between B.BookNoFrom And B.BookNoTo then select B.ChangeBookNo else select A.BookNo
- If 1 record in A (LEFT JOIN) B --> output many records then select the record that have smallest B.ChangeBookNo
Result of SQL with above data:
1111
2234
3234
thank you so much for any help/insight/direction you can offer.
September 20, 2010 at 9:28 pm
Give this a try:
UPDATE A1
SET bookno = COALESCE(ChangeBookNo,BookNo)
FROM A A1
OUTER APPLY ( Select Top 1 ChangeBookNo
From B
Where BookNO BETWEEN BookNoFrom and BookNoTo
Order by ChangeBookNo) oa
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 20, 2010 at 9:48 pm
September 21, 2010 at 7:13 am
You're welcome.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply