NESTED QUERY (3 COL, 1 TABLE) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

NESTED QUERY (3 COL, 1 TABLE)

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2009-02-04 : 14:01:49

Giving a table with this values

SELECT *
INTO ABC_TEST
FROM (
sELECT 'A' COL1, 'B' COL2, 'C' COL3 UNION
sELECT 'B' COL1, 'C' COL2, 'D' COL3 UNION
sELECT 'D' COL1, 'E' COL2, 'F' COL3 UNION
sELECT 'X' COL1, 'Y' COL2, 'Z' COL3 UNION
sELECT 'Q' COL1, 'R' COL2, 'Z' COL3) ABC

Where all three columns are available options. And col2 and col3 are nested options of col1. How can I retrieve all available options when the selected options are 'A' and 'X'? The expected result is A,B,C,D,E,F,X,Y,Z

A candle loses nothing by lighting another candle

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2009-02-04 : 15:58:08

I don't like this solution but it works

Select *
into abc_test_selected
From abc_test a
Where exists (
Select 1 From abc_test b Where col1 in ('A','X')
and (a.col1 = b.col1 or
a.col1 = b.col2 or
a.col1 = b.col3 ))

While @@rowcount>0
Insert into abc_test_selected
Select * from abc_test a Where col1 in(
select col1 from abc_test_selected union
select col2 from abc_test_selected union
select col3 from abc_test_selected) and not exists
( Select 1 from abc_test_selected b
Where a.col1 = b.col1)

select col1 from abc_test_selected union
select col2 from abc_test_selected union
select col3 from abc_test_selected

A candle loses nothing by lighting another candle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-04 : 16:04:52

What about this sample data?

SELECT *
INTO ABC_TEST
FROM (
sELECT 'A' COL1, 'B' COL2, 'C' COL3 UNION
sELECT 'B' COL1, 'C' COL2, 'D' COL3 UNION
sELECT 'D' COL1, 'E' COL2, 'Q' COL3 UNION
sELECT 'X' COL1, 'Y' COL2, 'Z' COL3 UNION
sELECT 'Q' COL1, 'R' COL2, 'Z' COL3) ABC

For B, there is a reference to D, which has a reference to Q.

E 12°55'05.63"
N 56°04'39.26"

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2009-02-06 : 11:51:42

then the answer should be a,b,c,d,e,q,r,z,x,y

A--B--C
---B--C--D
---------D--E--Q
---------------Q--R--Z
X--Y--Z

the actual table is a rule table that works as a desition tree. Each option is a select statement if it returns data then the condition was meet so the first option (col2) is executed else the other statement (col3) and on. I was asked to retrieve all error codes available for a certain product. So I had to select the rule declared for the product and the nested rules of those rules to retrieve from those records the error codes


/*****************************
 |      A               X    |
 |     / \             / \   |
 |    B   C           Y   z  |
 |   / \                     |
 |  C   D                    |
 |     / \                   |
 |    E   Q                  |
 |       / \                 |
 |      R   Z                |
*****************************/

A candle loses nothing by lighting another candle

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 09:38:19

what should happen if COL3 of first row is null?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources