Hi Everybody I am new to SQLSer

[Guest_imported]

Hi Everybody

I am new to SQLServer, i got struck at here, i have a self referencial table with columns A and B.
the structure and table data is given here. the query which i have given here is an oracle equivalent
i would like to have a query which is specific to SQLServer and MS-Access too (both).

Thanks in Advance
Ashok


SQL> DESC NUMS;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER

SQL> select * from nums order by 1,2;

A B
---------- ----------
1 0
2 3
2 4
2 4
2 5
2 41
2 42
3 6
3 7
3 8
3 9

A B
---------- ----------
3 10
4 11
4 12
4 13
4 14
5 15
5 16
5 17
5 18
6 19
6 20

A B
---------- ----------
6 21

23 rows selected.

SQL> SELECT DISTINCT A ,B FROM NUMS
2 CONNECT BY PRIOR B=A START WITH A=2
3 ORDER BY 1
4 /

A B
---------- ----------
2 3
2 4
2 5
2 41
2 42
3 6
3 7
3 8
3 9
3 10
4 11

A B
---------- ----------
4 12
4 13
4 14
5 15
5 16
5 17
5 18
6 19
6 20
6 21

21 rows selected.

SQL> SELECT DISTINCT A ,B FROM NUMS
2 CONNECT BY PRIOR B=A START WITH A=3
3 ORDER BY 1
4 /

A B
---------- ----------
3 6
3 7
3 8
3 9
3 10
6 19
6 20
6 21

 

[wengyan]

In SQL Server 2000
Is it right to you?

DROP TABLE TMP
GO

CREATE TABLE TMP
(A INT,
B INT)

GO

INSERT INTO tmp values(1,0)
INSERT INTO tmp values(2,3)
INSERT INTO tmp values(2,4)
INSERT INTO tmp values(2,41)
INSERT INTO tmp values(3,6)
INSERT INTO tmp values(3,7)
INSERT INTO tmp values(3,8)
INSERT INTO tmp values(3,8)
INSERT INTO tmp values(4,10)

select DISTINCT TMP.A,TMP_1.B from TMP
INNER JOIN TMP TMP_1
ON TMP_1.A=TMP.A WHERE TMP.A>=2
ORDER BY 1

A B
----------- -----------
2 3
2 4
2 41
3 6
3 7
3 8
4 10 WENG YAN