Using SSRS 2005, is there anyway to embed a table in a table and link the two together in a parent child relationship?
R
WHat do you want to achieve ? It sure can be accomplished by using the standard functionality.HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Hello,
I have the following code which will show all bottom level leaf nodes of the hierachy:
SELECT name
FROM tree
WHERE rgt = lft + 1;
I'd like to be able to filter results by a node. For example in a tree such as:
Products
ReleaseProduct
Release1
Release build 1
Release build 2
Release 2
Release 2 build 1
Release 2 build 2
Build Product
Build 1
Build 2
If Build 2 is chosen (any node with no children) I'd like to just show the Buuild 2, if ReleaseProduct is chosen Release build 1, Release build 2, Release 2 build 1 and Release 2 build 2 will be shown and if BuildProduct is chosen I'd like to display Build 1, Build 2.
I understand the prinicipals but my SQL is quite lacking anything further than the select, where statements. If anyone could please lend me a little advice on how to go about this I would be very grateful!
Thanks :)
Hello,
Can you post the schema of the table in question and what version of SQL Server you are using?
If 2005, a recursive CTE sounds like it may suit, otherwise a more "creative" solution may apply. let us know the specifics and I'm sure we can help out.
Cheers,
Rob
|||Thank's for the quick reply!The schema is as follows:
CREATE TABLE site_category(
site_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);
So a site may be a root, parent or child depending on the left and right values of the nodes in the hierachy. I'm using 2005 Express.
Thanks for the help!|||
Hello,
I don't know what lft or rgt is, but I'm going to assume that they contain the site_id of the parent node. So, to simplify this, let's call it ParentSiteID:
with Sites(SiteName, site_id, ParentID, NestLevel)
AS
(
SELECT [name], site_id, parentSiteID, 0
FROM site_category
WHERE [name] = 'Site123'
UNION ALL
SELECT sc.[Name], sc.Site_ID, s.Site_ID,(NestLevel + 1)
FROM Sites s
JOIN site_category sc ON s.Site_ID = sc.ParentSiteID
)
SELECT *
FROM Sites
The above example will return "Site123" and all child nodes therein (including any nested relationships). The NestLevel column indicates how deep the nesting level is. You'll need to adjust this to cater for your lft/rgt columns...
Cheers,
Rob
|||The lft and rgt fields store values used to determine the level in the hierachy. The example from the MySQL site I am using as a guide is:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
Following this I have got to the heading 'Finding the Depth of the Nodes' which produces the results I am after.
Where I'm having trouble is the heading 'Find the Immediate Subordinates of a Node' which is exactly what I need and is explained with code but I just can't figure it out! I feel there may be some subtle differences in the SQL used in this MySQL example and the TSQL SQL Server is expecting. Not to mention my SQL knowledge isn't great at this point!
I havn't tried your example but feel this post may offer a better explanation as (I may be wrong) your example looks like it assumes I am using an Adjacency List Model.
I appreciate your time! :)
|||Hello,
OK, I understand what you're trying to do:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT TOP 100 node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;
Does that do what you want?
Cheers,
Rob
|||That works exactly how I want!
Is the TOP keyword and value an approximation of the rows to be returned to be returned, as the complete result set is not loaded into memory?
Thanks :)
|||Actually, the only reason to use TOP in the sub query is because without it, you cannot use an order by. So you could actually remove it and the corresponding order by:
SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;
Cheers,
Rob
|||Oh I see, Thanks again!sql1. Is it possiable to delete a record from the parent table.It is
even ok to me , if it leads to the deletion of all the child tables .
2.I 've come across a situiation where the name of the table is to be
supplied by the variable in my sp ,like
@.t = 'table1'
select * from @.t -- it gives the error.
--actually i want "select * from table1" & in my sp i gave as
So i am forced to give the table name from the sub query.
how can i acheive this thru query where the table name has to be
supplied to the from clause from the sub query
With Thanks
Raghuraman.C"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0401152012.7e068064@.posting.google.c om...
> Hai
> 1. Is it possiable to delete a record from the parent table.It is
> even ok to me , if it leads to the deletion of all the child tables .
>
> 2.I 've come across a situiation where the name of the table is to be
> supplied by the variable in my sp ,like
>
> @.t = 'table1'
> select * from @.t -- it gives the error.
> --actually i want "select * from table1" & in my sp i gave as
> So i am forced to give the table name from the sub query.
> how can i acheive this thru query where the table name has to be
> supplied to the from clause from the sub query
> With Thanks
> Raghuraman.C
I'm not sure if I understand your questions completely, but I think this is
what you want:
1. If possible, you can declare your foreign keys with ON DELETE CASCADE
(see Books Online). Then when you delete a row from the parent table, any
child records will automatically be deleted also. If that isn't a good
solution in your situation, you can consider triggers, or doing all deletes
through a stored procedure which deletes rows from the tables in the correct
order.
2. See this link:
http://www.sommarskog.se/dynamic_sql.html
Simon|||Dear simon,
For the option 1.
I could not reach any word combinations like DELETE ON CASCADE for the
foriegn keys in the books on line in sqlserver 7.0. Are u telling with
SQLSERVER2000.
If so, what the way in sqlserver7.0
With regards
Raghu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Raghu Raman <raghuraman_ace@.rediffmail.com> wrote in message news:<400cd41a$0$70304$75868355@.news.frii.net>...
> Dear simon,
> For the option 1.
> I could not reach any word combinations like DELETE ON CASCADE for the
> foriegn keys in the books on line in sqlserver 7.0. Are u telling with
> SQLSERVER2000.
> If so, what the way in sqlserver7.0
> With regards
> Raghu
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
Yes, cascading DRI is only available in SQL2000. I should have
mentioned that, but please always state which version of MSSQL you're
using. In SQL7, you can either use triggers or stored procedures. If
you can ensure that your applications will always use a stored proc
for deletions, then it is probably an easier solution. But if you have
different applications/clients, and you can't be sure that they will
always use the proc, then a trigger is more reliable.
Simon|||Hai,
I did that cascading deletion using sproc..& it is nice
Thanks for concurrent reply
With Regards
Raghu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||>> 1. Is it possible to delete a record [sic] from the parent table.
<<
Tables are not files; rows are not records; columns are not fields.
They are compltely different concepts! There are "referenced" and
"referencing" tables in SQL. That "parent" and "child" terminology
belongs to network DBMS models.
You can set up DRI actions that will cascade a deletion from a
referenced table to all the referencing tables.
>> 2.I 've come across a situiation where the name of the table is to
be
supplied by the variable in my stored procedure ... <<
NO! The short answer is use slow, proprietrary dynamic SQL to kludge
a query together on the fly with your table name in the FROM clause.
The right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your applications a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and
applications do not work at that level, if you want to have any data
integrity.
Yes, you can write a program with dynamic SQL to kludge something like
this. it will last about a year in production and then your data
integrity is shot.
