July 20, 2008 at 10:57 pm
I have two varchar(7) fields in one table 'xxxx111', and 'xxxx222', that represent a starting value, and ending value. There is also a field in this table that reflects a name 'John Smith'. I have another table that has a varchar(7) field that would have an 'xxxx113' and needs the name from the first table. Any help would be appreciated.
July 20, 2008 at 11:37 pm
[font="Verdana"]If you could provide some sample data's & exact table schema, it will be helpful.[/font]
Regards..Vidhya Sagar
SQL-Articles
July 21, 2008 at 4:34 am
Is there any PK FK relationship between the two tables?
If yes, create a join between the tables using that column.
Atif Sheikh
July 21, 2008 at 7:24 am
Unfortunately there is no solid relationship between the two tables. I am trying to fix the name field, based on the range from the first table with the begin_value, and end_value.
Table ManagersInfo
ManagerName varchar(30),
begin_value varchar(7), --Employee Number
end_value varchar(7) --Employee Number
'John Smith', 'xxx1111', 'xxx1133'
The information in this table is solid.
Table WorkInfo
ManagerName varchar(30),
EmployeeNumber varchar(7)
'j' smith', 'xxx1121'
The application that managed the work allowed the user to type a manager name instead of a drop down selection list or verifying the name was correct. I am trying to go back, and fix the data.
July 21, 2008 at 11:55 am
You could do:
select ManagerName
from ManagersInfo
inner join WorkInfo
on EmployeeNumber between begin_value and end_value
Does that get you what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 22, 2008 at 10:41 pm
That helps, I was making it more complicated than it needed to be. The only problem I am having now is that since that range has both numbers, and characters, the match is returning more rows than it should. I was a bad program, but the letters can increment as well as the numbers:
NL1111 to NX1111 for example. I tried to take the ASCII value, and make a string then cast it as a decimal number, but it still is not getting the correct range.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply