difficult query

sorry, hit "send" by mistake previously.
Hi all,
My codes to achieve the following is very complicated
(about 200 short lines). Can anyone think of a better way?
--DATA--
create table myTableA(
myID int,
mykey varchar(5)
)
go
insert into myTableA
values (1,'01A')
insert into myTableA
values (2,'01A')
insert into myTableA
values (3,'01A')
go
create table myTableB(
mykey varchar(5),
mystyle varchar(3)
)
go
insert into myTableB
values ('01A', '202')
insert into myTableB
values ('01A', '204')
insert into myTableB
values ('01A', '206')
--Required Result--
the required result need to look like this (without
duplicates):
myID myKey myStyle
1 01A 202
2 01A 204
3 01A 206
--
Thank you in advance,
Annathere is a problem in that there is no apparent way to
know that row 1 of MyTableA matches only to row 1 of
MyTableB assuming the linkage is the common column mkKey
is there some other business logic involved that would
shed light on this ?
>--Original Message--
>sorry, hit "send" by mistake previously.
>Hi all,
>My codes to achieve the following is very complicated
>(about 200 short lines). Can anyone think of a better way?
>--DATA--
>create table myTableA(
>myID int,
>mykey varchar(5)
>)
>go
>insert into myTableA
>values (1,'01A')
>insert into myTableA
>values (2,'01A')
>insert into myTableA
>values (3,'01A')
>go
>create table myTableB(
>mykey varchar(5),
>mystyle varchar(3)
>)
>go
>insert into myTableB
>values ('01A', '202')
>insert into myTableB
>values ('01A', '204')
>insert into myTableB
>values ('01A', '206')
>--Required Result--
>the required result need to look like this (without
>duplicates):
>myID myKey myStyle
>1 01A 202
>2 01A 204
>3 01A 206
>--
>Thank you in advance,
>Anna
>
>.
>|||Anna
If you want in your output ID as sequence so try this one
select *,(select count(*) from myTableb v where v.mystyle<=myTableb.mystyle)
from myTableb
"Anna" <lliming12@.yahoo.com> wrote in message
news:070901c3614a$fff4e6e0$a601280a@.phx.gbl...
> Due to historical reasons, the data is the way it is. It
> is not necessary that row1 of myTableA matches only to
> row1 of myTableB. The important part is for the three rows
> in myTableA to match to three different rows in myTableB.
> thanks,
> >--Original Message--
> >there is a problem in that there is no apparent way to
> >know that row 1 of MyTableA matches only to row 1 of
> >MyTableB assuming the linkage is the common column mkKey
> >
> >is there some other business logic involved that would
> >shed light on this ?
> >
> >>--Original Message--
> >>sorry, hit "send" by mistake previously.
> >>
> >>Hi all,
> >>
> >>My codes to achieve the following is very complicated
> >>(about 200 short lines). Can anyone think of a better
> way?
> >>
> >>--DATA--
> >>create table myTableA(
> >>myID int,
> >>mykey varchar(5)
> >>)
> >>go
> >>insert into myTableA
> >>values (1,'01A')
> >>insert into myTableA
> >>values (2,'01A')
> >>insert into myTableA
> >>values (3,'01A')
> >>go
> >>create table myTableB(
> >>mykey varchar(5),
> >>mystyle varchar(3)
> >>)
> >>go
> >>insert into myTableB
> >>values ('01A', '202')
> >>insert into myTableB
> >>values ('01A', '204')
> >>insert into myTableB
> >>values ('01A', '206')
> >>
> >>--Required Result--
> >>the required result need to look like this (without
> >>duplicates):
> >>
> >>myID myKey myStyle
> >>1 01A 202
> >>2 01A 204
> >>3 01A 206
> >>
> >>--
> >>
> >>Thank you in advance,
> >>Anna
> >>
> >>
> >>
> >>.
> >>
> >.
> >