Getting children and grandchildren of item

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 2008 Forums

Transact-SQL (2008)

Getting children and grandchildren of item

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-04-05 : 14:08:48

Let me first start by saying "thank you in advance" for reading this. This question has bogged me down for far too long.

In this database, there are two tables tb_assets and tb_locations. Simplified structure is as follows:

tb_assets
f_assetname
f_assetparent (integer)

tb_locations
f_locationID (integer)
f_locationparent (integer)
f_locationname

In tb_locations each location may have parents, so the lineage is potentially infinite. An example is here:

The database structure of this would be as follows:

I would like to be able to query for a particular location (say Athens) and get all the the assets assigned to Athens, Parthenon, and Agora. In another example, I may want to query for Louvre and get all the assets for Louvre and Lens. (For example, I would like to query where f_locationID = 5 and get all the assets where f_assetparent = 5 and where f_assetparent = 6 (since Lens (6) is a parent of Louvre (5)).

In tb_assets, the column f_assetparent equals a value tb_locations for f_locationID.

Again, thank you in advance. I don't even want to post the queries I had tested because they are obviously quite moot since I was not able to achieve any success.

- Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 14:16:22

have a look at common table expressions

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2012-04-05 : 14:51:52

I played with CTEs for a while without any luck. If I had a single table with the information combined into it would have a solution as I have done that in the past. My problem this time is most definitely adding to the confusion of having two tables to JOIN.

EDIT:

Well, thanks for the push off the cliff :)...I guess that's what it took. I wasn't using the JOIN in the final select portion of the CTE. Final code below

When I was just about ready to throw my hands in the air and start smoking again, I finally have a solution.


;with cte_assets as (
       select a.f_locationid
              ,a.f_locationparent
              ,a.f_locationname
              ,0 as [lev]
              ,convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
       from   tb_locations a
       where f_locationID = '1' 

       UNION ALL
       --recusive portion
       select a.f_locationid
              ,a.f_locationparent
              ,a.f_locationname
              ,c.[lev] + 1
              ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
       from   cte_assets c
       join   tb_locations a
              on a.f_locationparent = c.f_locationID
)
--final select

	select *
	from cte_assets c
	JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID
	ORDER BY f_assetname DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 15:06:13

sounds like this to me



DECLARE @yourvalue int

SET @yourvalue=5 -- example value

;With AssetHier
AS
(
SELECT a.f_assetname,l.f_locationname,l.f_locationparent
FROM tb_assets a
INNER JOIN tb_locations l
ON l.f_locationID = a.f_assetparent
WHERE l.f_locationID = @yourvalue

UNION ALL

SELECT a.f_assetname,l.f_locationname,l.f_locationparent
FROM AssetHier h
INNER JOIN tb_locations l
ON l.f_locationID = h.f_locationparent
INNER JOIN tb_assets a
ON a.f_assetparent = l.f_locationID
)

SELECT *
FROM AssetHier

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 15:09:04

Oh..ok So you've sorted it out
great

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources