Nested join

ID IAParent Entry Level
110 95 Request [NULL] 4
111 95 Install [NULL] 4
112 95 Remove [NULL] 4
113 76 Power [NULL] 5
114 109 Power [NULL] 5
115 109 Display [NULL] 5
116 109 Keyboard/Touchpad [NULL] 5
117 109 Docking Station [NULL] 5
118 109 Memory [NULL] 5
119 109 Reimage Machine [NULL] 5
120 109 CD/Floppy Drive [NULL] 5
121 109 General Diagnostics [NULL] 5
122 109 Modem [NULL] 5
123 109 Setup/Configuration [NULL] 5
124 109 Vendor Repair [NULL] 5
125 109 Virus [NULL] 5
126 76 Miscellaneous [NULL] 5
127 109 Miscellaneous [NULL] 5
128 112 User Leaving Firm [NULL] 5
129 110 Loaner [NULL] 5
Hello all,
I have a question about self join. From the table info above, there are
five levels that are in the same table.
For example...ID 129 has a parent of 110... then 110 has another parent
of 95 and so on till you get to level 1..
how can I write a join query that will show me level 1 through 5?
Thanks!Level is redundant in your table because it can obviously be derived by
counting the levels above each node. For that reason it would probably be
wise to drop the Level column. Here's one solution:
SELECT T.id, T.iaparent, T.entry,
SIGN(ISNULL(id1,0))+SIGN(ISNULL(id2,0))+
SIGN(ISNULL(id3,0))+SIGN(ISNULL(id4,0))+
SIGN(ISNULL(id5,0))-
CASE T.id
WHEN id1 THEN 0
WHEN id2 THEN 1
WHEN id3 THEN 2
WHEN id4 THEN 3
WHEN id5 THEN 4
END AS level
FROM your_table AS T,
(SELECT T1.id, T2.id, T3.id, T4.id, T5.id
FROM your_table AS T1
LEFT JOIN your_table AS T2
ON T1.iaparent = T2.id
LEFT JOIN your_table AS T3
ON T2.iaparent = T3.id
LEFT JOIN your_table AS T4
ON T3.iaparent = T4.id
LEFT JOIN your_table AS T5
ON T4.iaparent = T5.id
WHERE T1.id = @.id) AS L(id1,id2,id3,id4,id5)
WHERE id IN (id1,id2,id3,id4,id5)
ORDER BY level ;
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You also need to get a copy of TREES & HIERARCHIES IN SQL or to Google
"Nested Set Model" for trees.|||Levels can be computed and you can do this in one simple self-joined
query for any level.

Negative values not displayed in series

Hi,
I am using graphical report to display data. If the values are negative (
i.e less than 0) then the graph is not displaying the series values. If the
values are positive then the graph is displaying series values.
I need to display the series values even though they are negative.
How to do this.Any url/help/suggestions urgently required.
Thanks and Regards,
Rajesh Yennam.
HA, India.I encountered this the other day. If you go to the chart properties' Y-Axis
tab the minimum scale is set to 0. To see negative numbers remove the 0
from the text box.
Matt
"Rajesh Yennam" <RajeshYennam@.discussions.microsoft.com> wrote in message
news:2AF40A1D-DF85-4B72-AE86-754502BFEA1F@.microsoft.com...
> Hi,
> I am using graphical report to display data. If the values are negative (
> i.e less than 0) then the graph is not displaying the series values. If
the
> values are positive then the graph is displaying series values.
> I need to display the series values even though they are negative.
> How to do this.Any url/help/suggestions urgently required.
> Thanks and Regards,
> Rajesh Yennam.
> HA, India.

negative sign (unary operator) not displayed for numeric data types

I have a table with a field that has a numeric data type (15,2) and length of 9. The problem is that it won't display the actual negative sign for any values less than 0 when running a query. Any ideas? I've used Query Analyzer as well as Access.
Thanks.Huh?

DECLARE @.x decimal(15,4)
set @.x = -12345.1234
SELECT @.x|||Maybe this will help.

For instance, one customer in the table has values like this:

4380.00
4380.00
8760.00
4380.00
4380.00
23360.00
23360.00

but actually it should be:

4380.00
-4380.00
8760.00
-4380.00
-4380.00
23360.00
-23360.00

which nets to a total of 0. And if I change the query to a group query and perform a sum on this revenue field I will get 0 but if I do a normal select I'll get all positives. Sql server knows that some of the revenue is negative it just doesn't display the - sign infront of it.

Btw:

DECLARE @.x decimal(15,4)
set @.x = -12345.1234
SELECT @.x

works correctly.|||How are you viewing the data? I betcha it's a front end issue?

Or are yu seeing this in QA.

Read the huint sticky at the top of the board and post what it asks for|||"select [Value] from [YourTable] where [Value] < 0" returns what?|||I've gotten the same results using Access as the front end linking the sql server table and using Query Analzyer. I will take a look at that sticky.

If I run the same query with the < 0 criteria I will get values returned that look positive. I even copied and pasted the values into Excel and they still show up as positive values.

If I change the data type to float it works fine, but for some reason numeric data type won't display negative values.|||are you sure you aren't running this:

select abs(mycol) from mytable where mycol < 0

in any case I can't repro it:

declare @.t table (col decimal(15,2))
insert into @.t
select -12.11 union all select 234.33 union all select -44.444
select * from @.t

col
------------
-12.11
234.33
-44.44|||Sorry etaktaf, but unless you can provide us with some code for replicating this problem (create table, populate table, select results...), then I don't think we can help you any further.|||In other words post some examplkes as the hint link states