Showing posts with label mistake. Show all posts
Showing posts with label mistake. Show all posts

Wednesday, March 21, 2012

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
> >>
> >>
> >>
> >>.
> >>
> >.
> >