table1 have fields of 1
fld1
aa
bb

table2 have fields of 2
fld1   fldx
aa      1
aa      3
aa      8
bb      1

table3 have fields of 2
fld1   fldy
aa      k
aa      b
aa      c
bb      g


i want to make following table using query.
fld1  fldx   fldy
aa     1      k
aa     3      b
aa     8      c
bb     1      9


how query???


*** Sent via Developersdex http://www.developersdex.com ***

There doesn't seem to be any relationship between table2 and table3.
Are you saying that you want to match the first row in table2 for fld1
with the first row in table3 for fld1? If so, you could have to
calculate a row number for each row in tables2 and table3 and then use
this row number to join between the two tables.

In SQL Server 2005 you can use ROW_NUMBER() OVER() to generate your row
numbers. In SQL Server 2000, you would have to do something like:

SELECT a.*,
              RowNum = (SELECT COUNT(*) + 1
                                FROM table1 b
                                WHERE b.fld1 = a.fld1
                                 AND b.fldx < a.fldx )
FROM table1 a

Chris

tom taol wrote:
Show quoteHide quote

Conversion failed when converting datetime from character string.
dynamic SQL versus a huge IF structure
Is there an eval() function in sql server 2000?
Variable table name in a cursor declaration
Comparing two tables