Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: Help w/ sql query

    Hey all,

    I'm really not that great with sql queries so I find myself needing some help on this one. So first off here is the basic data structure of what I'm dealing with


    Assets
    --------
    id,name

    Rel
    --------
    parent(f-key from assets.id),child(f-key from assets.id)


    So when a relationship is established a new row gets created on the rel table. One row for each relationship (so there will be duplicates in the parent field, or the child field, but never both at the same time). What I've been trying to do is write query which returns simply parent.asset.name and child.asset.name. I'm too stupid to get it working, please help!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why is your relation data in a separate table?
    Can a record be both the Parent and the Child of the same record?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2009
    Posts
    2
    I have no idea why the application was designed this way. And I think the answer is 'no' to your second question.

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    try to use commontable expressions (cte) to get the parent-child relations

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    create table asset(
    id integer not null,
    name nvarchar(50) not null,
    constraint pk_asset primary key (id)
    );
    
    create table rel(
    id_parent integer not null,
    id_child integer not null,
    constraint pk_rel primary key (id_parent, id_child),
    constraint parent_NEQ_child CHECK(id_parent <> id_child)
    );
    
    insert into asset(id, name) values (1, 'mother');
    insert into asset(id, name) values (2, 'father');
    insert into asset(id, name) values (3, 'son');
    insert into asset(id, name) values (4, 'daughter ');
    insert into asset(id, name) values (5, 'friend');
    
    insert into rel(id_parent, id_child) values (1, 3);
    insert into rel(id_parent, id_child) values (2, 3);
    insert into rel(id_parent, id_child) values (1, 4);
    insert into rel(id_parent, id_child) values (2, 4);
    
    select parent.name as parent_name, 
    	child.name as child_name
    FROM rel 
    	INNER JOIN asset as parent ON
    		rel.id_parent = parent.id
    	INNER JOIN asset as child ON
    		rel.id_child = child.id
    ;
    // result:
    parent_name	child_name
    mother		son
    mother		daughter 
    father		son
    father		daughter
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Can a record be both the Parent and the Child of the same record?
    Quote Originally Posted by Wim
    constraint parent_NEQ_child CHECK(id_parent <> id_child)
    nice one, Wim

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    nice one, Wim
    I was concerned more about circular relationships.

    Pr0fiT, what version of SQL Server are you using? In 2000, you would need to use a temp table to expand this hierarchy. In 2005, you would use a Common Table Expression (CTE).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What I've been trying to do is write query which returns simply parent.asset.name and child.asset.name.
    Was my solution what you were looking for (parent/child)? or do you need an overview of all the relationships so also (grandparent - grandchild)?

    If you need grandparent - grandchild results, you have to use recursive SQL, by using CTE.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Wim
    If you need grandparent - grandchild results, you have to use recursive SQL, by using CTE.
    you don't ~have~ to, but it shore is nice
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This must be the 5th recursive SQL I wrote
    Kudos to Srinivas Sampath for his great article.
    Code:
    insert into asset(id, name) values (1, 'mother');
    insert into asset(id, name) values (2, 'father');
    insert into asset(id, name) values (3, 'son');
    insert into asset(id, name) values (4, 'daughter ');
    insert into asset(id, name) values (5, 'friend');
    insert into asset(id, name) values (6, 'grandmother');
    insert into asset(id, name) values (7, 'grandfather');
    insert into asset(id, name) values (8, 'great-grandmother');
    insert into asset(id, name) values (9, 'great-grandfather');
    
    insert into rel(id_parent, id_child) values (1, 3);
    insert into rel(id_parent, id_child) values (2, 3);
    insert into rel(id_parent, id_child) values (1, 4);
    insert into rel(id_parent, id_child) values (2, 4);
    insert into rel(id_parent, id_child) values (6, 1);
    insert into rel(id_parent, id_child) values (7, 1);
    insert into rel(id_parent, id_child) values (8, 6);
    insert into rel(id_parent, id_child) values (9, 6);
    
    -- show all ancestors of the daughter
    WITH ancestorCTE (Level, id_parent, parent_name, 
    		id_child, child_name) AS
     (
      -- Create the anchor query. This establishes the starting
      -- point
    	SELECT 0, rel.id_parent, parent.name as parent_name, 
    		rel.id_child, child.name as child_name
    	FROM rel 
    		INNER JOIN asset as parent ON
    			rel.id_parent = parent.id
    		INNER JOIN asset as child ON
    			rel.id_child = child.id
    	WHERE child.name = 'daughter'
    
    	UNION ALL
      -- Create the recursive query. This query will be executed
      -- until it returns no more rows
    	SELECT ans.level + 1, 
    	rel.id_parent, 
    	parent.name as parent_name, 
    	ans.id_child, 
    	ans.child_name as child_name
    	FROM ancestorCTE as ans
    		INNER JOIN rel ON
    			ans.id_parent = rel.id_child
    		INNER JOIN asset as parent ON
    			rel.id_parent = parent.id
     )
    SELECT * FROM ancestorCTE ORDER BY level asc;
    Code:
    -- result:
    Level	id_parent parent_name	id_child child_name
    0	1	mother		4	daughter 
    0	2	father		4	daughter 
    1	6	grandmother	4	daughter 
    1	7	grandfather	4	daughter 
    2	8	great-grandmother 4	daughter 
    2	9	great-grandfather 4	daughter
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's the microsoft article on recursive CTEs...

    Recursive Queries Using Common Table Expressions

    surprising clarity for a microsoft article, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •