subquery/correlated query question

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

SQL Server Development (2000)

subquery/correlated query question

Author

Topic

Aux
Starting Member

1 Post

Posted - 2003-01-06 : 10:34:25

Hello,

I've got a little problem with a query I couldn't solve.. It's really a complex query with serval nested querys, but the main problem can be described as followed:

I've got a table wich looks like this:
ID Value Relation (0 is no relation!)
1 Test 0
2 Sure 1
3 Thing 2

Now I want to make a query which gives te following result:

"Test Sure Thing"

I think this should be possible, because of the relations I made.
First I was thinking about something like:

Select value, (select value where ID = 2 from tabel) as value2, (select value where ID = 3 from table) as value3
from tabel
where ID = 1

That didn't work because it was not possible to use the value more than once. I also tried using aliases (a.value, b.value and c.value in the subquerys)

However this query wasn't really what I wanted, before this is Tried:

select A.value, B.value, C.value from A.tabel where A.relation IN
(select B.ID from B.tabel where B.relation IN
( select C.ID from C.table where C.relation = 2)
)

But.. the B an C aliases are not useable outside their ()

Anyone a solution?

With kind regards,
Aux

1fred
Posting Yak Master

158 Posts

Posted - 2003-01-06 : 10:44:56

Here is a solution, may not work with your original dataset.

select case
when value = 'test' then value
when value = 'sure' then value
when value = 'thing' then value end as value
from test

tool
Starting Member

26 Posts

Posted - 2003-01-06 : 10:50:08

Something like this may work for you:


DECLARE @string varchar(1000)
SELECT @string=COALESCE(@string+' ','')+value FROM table

.
Check out this excellent article:[url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-06 : 11:42:25

What makes the problem difficult is that you are not just ordering by an ID, but you need to use the 'relations' to jump from record to record. In the example data, it should be returned by the same order as the ID, but for real data that probably won't usually be the case.

Thus, you need something that's sort of recursive. Below is an iterative way of doing it, that will travel down the relations no matter how deep. I added some records so you can see that the ID is not order in which things are returned.

Also, you can start from an arbitrary starting ID.

-----------------------

declare @t table (id int primary key, Val varchar(10), Relation int)

insert into @t
select 1, 'Test', 0 union
select 2, 'Sure', 1 union
Select 3, 'Thing', 2 union
Select 5, 'Out', 3 union
Select 4, 'Of', 5 union
Select 6, 'Order', 4

/* to return the string, starting at a particular ID */

declare @currID int;
declare @Result varchar(100);

set @CurrID = 1 /* the starting point */
set @Result = '';

while @CurrID != 0
begin
select @Result = @Result + A.Val + ' ', @CurrID = ISNULL(B.ID,0)
FROM @t A
LEFT OUTER JOIN @t B
ON A.ID = B.Relation
end

print @Result

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources