CTE(common table expressions)...help needed 1

[yorge]

Hi Guys,

I'm fairly new to CTE and I would like to ask a little help.
Given the following tables:

ITEM Table

ITEMID(INT) ITEMREFERENCEID(INT)
--------------------------------------
1 48
2 50
3 51
4 52
5 53
6 54
7 55
8 56
9 57
10 58
11 59
12 60
13 61
14 62
15 63
--------------------------------------

ITEM_HIERARCHY table (PARENTNODEID is self reference to NODEID)

NODEID(INT) ITEMID(INT) PARENTNODEID(INT) VISIBLE(INT)
--------------------------------------------------------------------
1 1 NULL 1
2 2 1 1
3 3 1 1
4 4 2 NULL
5 5 2 1
6 6 2 1
7 7 4 1
8 8 6 1
9 9 3 1
10 10 9 1
11 11 9 1
12 12 3 1
13 13 NULL 1
14 14 13 1
15 15 13 2
--------------------------------------------------------------------

If ITEMREFERENCEID = 48 (the anchor), I wanted to return all ITEMS (rows) belonging to the 48 hierarchy (on the ITEM_HIERARCHY table, it should be NODEID 1 to 12)

Can anybody please suggest or show me a query how can this be done using CTE (common table expressions)?

below is the query im trying to work on, it's not getting what I want but it might help any of you guys one way or the other.

Select itemreferenceid item
where itemid in (Select itemid from item_hierarchy
where (Visible IS NULL OR Visible = 1)
and parentnodeid = ( Select nodeid
from item_hierarchy
where itemid = ( Select Itemid
from item
where itemreferenceid=48)))

Thanks in advance,
Yorge

 

[yorge]

sorry, I needed to return the ITEM.ITEMREFERENCEID for all rows return when ITEMREFERENCEID = 48 just like what I have on the sample query I posted.

 

[imex]

Hi,

Try something like this:

with CTE_Recursive as
(
    select 
        i.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from ITEM as i
    inner join ITEM_HIERARCHY as h
        on h.ITEMID = i.ITEMID
    where
        i.ITEMREFERENCEID = 48
        
    union all
    
    select
        h.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from CTE_Recursive as r
    inner join ITEM_HIERARCHY as h
        on h.PARENTNODEID = r.ITEMID
    inner join ITEM as i
        on i.ITEMID = h.ITEMID
)

select * from CTE_Recursive

Hope this help.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[yorge]

thanks imex for the help...you point me to the right direction.
I'm face with another dilemma now. A reverse of what I have above is needed. Meaning, the child's ITEMREFERENCEID will be given and then the topmost parent ITEMREFERENCEID will be return.
So if ITEMREFERENCEID = ITEM.ITEMREFERENCEID = 59, then it will return ITEMREFERENCEID = 48
Can you please modify your code above to support this?

thanks in advance,
yorge

 

[yorge]

Im not sure if CTE will be ideal for what I need (get the topmost parent node) or a regular sql statement will do.

 

[imex]

Try:

with CTE_Recursive as
(
    select 
        i.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from ITEM as i
    inner join ITEM_HIERARCHY as h
        on h.ITEMID = i.ITEMID
    where
        i.ITEMREFERENCEID = 59
        
    union all
    
    select
        h.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from CTE_Recursive as r
    inner join ITEM_HIERARCHY as h
        on h.ITEMID = r.PARENTNODEID
    inner join ITEM as i
        on i.ITEMID = h.ITEMID
)

select * from CTE_Recursive
order by PARENTNODEID

Hope this help.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[yorge]

Hi imex, it seems it's not working. Output is 1 row and the itemreferenceid = 59 not 48

 

[imex]

Hi,

Sorry but I do not understand what you said.
Doing a test with the script below got 4 rows in the result:

declare @ITEM Table
(ITEMID INT, ITEMREFERENCEID INT);
insert into @ITEM values
(1, 48),
(2, 50),
(3, 51),
(4, 52),
(5, 53),
(6, 54),
(7, 55),
(8, 56),
(9, 57),
(10, 58),
(11, 59),
(12, 60),
(13, 61),
(14, 62),
(15, 63);

declare @ITEM_HIERARCHY table 
(NODEID INT, ITEMID INT, PARENTNODEID INT, VISIBLE INT);

insert into @ITEM_HIERARCHY values
(1, 1, NULL, 1),
(2, 2, 1, 1),
(3, 3, 1, 1),
(4, 4, 2, NULL),
(5, 5, 2, 1),
(6, 6, 2, 1),
(7, 7, 4, 1),
(8, 8, 6, 1),
(9, 9, 3, 1),
(10, 10, 9, 1),
(11, 11, 9, 1),
(12, 12, 3, 1),
(13, 13, NULL, 1),
(14, 14, 13, 1),
(15, 15, 13, 2);

with CTE_Recursive as
(
    select 
        i.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from @ITEM as i
    inner join @ITEM_HIERARCHY as h
        on h.ITEMID = i.ITEMID
    where
        i.ITEMREFERENCEID = 59
        
    union all
    
    select
        h.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from CTE_Recursive as r
    inner join @ITEM_HIERARCHY as h
        on h.ITEMID = r.PARENTNODEID
    inner join @ITEM as i
        on i.ITEMID = h.ITEMID
)

select * from CTE_Recursive
order by PARENTNODEID

Hope this help.



[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[yorge]

Hi Imex

So sorry but it seems the sample data I gave above is wrong. It should be like this:

ITEM

ITEMID(INT) ITEMREFERENCEID(INT)
--------------------------------------
1 1
2 48
3 51
4 52
5 53
6 54
7 55
8 56
9 57
10 58
11 59
12 60
13 61
14 62
15 63
--------------------------------------

ITEM_HIERARCHY (PARENTNODEIS is self reference to NODEID)

NODEID(INT) ITEMID(INT) PARENTNODEID(INT) VISIBLE(INT)
--------------------------------------------------------------------
1 1 NULL 1
2 2 1 1
3 3 2 1
4 4 2 NULL
5 5 4 1
6 6 4 1
7 7 4 1
8 8 6 1
9 9 3 1
10 10 9 1
11 11 9 1
12 12 3 1
13 13 1 1
14 14 13 1
15 15 13 2
--------------------------------------------------------------------

The root/topmost node is ITEMID = 1 (since parentnodeid = null)
ITEMIDs 2 and 13 are topmost children.
if ITEMREFERENCEID = 59, then it will return ITEMREFERENCEID = 48 (the topmost child where 59 belongs)
or if
if ITEMREFERENCEID = 63, then it will return ITEMREFERENCEID = 61 (the topmost child where 63 belongs)

TIA,
Yorge

 

[imex]

Try:

with CTE_Recursive as
(
    select 
        i.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from @ITEM as i
    inner join @ITEM_HIERARCHY as h
        on h.ITEMID = i.ITEMID
    where
        i.ITEMREFERENCEID = 59
        
    union all
    
    select
        h.ITEMID,
        i.ITEMREFERENCEID,
        h.PARENTNODEID
    from CTE_Recursive as r
    inner join @ITEM_HIERARCHY as h
        on h.ITEMID = r.PARENTNODEID
    inner join @ITEM as i
        on i.ITEMID = h.ITEMID
)

select top 1 * from CTE_Recursive
where PARENTNODEID is not null
order by PARENTNODEID

Hope this help.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[yorge]

hi imex, thanks for the help...I modified your code on this part:

inner join @ITEM_HIERARCHY as h
on h.ITEMID = r.PARENTNODEID

to

inner join @ITEM_HIERARCHY as h
on h.NODEID = r.PARENTNODEID

and it work like a charm