Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2008
    Posts
    6

    Unanswered: double self outer join question

    Hello. I'm building a simple family tree database. The main tables are the individual table (containing data about individuals), and the parent table, which is used as a join table, allowing the individual table to join to itself, therefore allowing individuals to be related to their parents. The parent table has a primary key constructed of two foreign keys to the individual_id pk of the individual table, one called individual_id and one parent_id (thus containing a reference to the individual's parent).

    CREATE TABLE [dbo].[individual](
    [individual_id] [int] IDENTITY(1,1) NOT NULL)

    CREATE TABLE [dbo].[parent](
    [individual_id] [int] NOT NULL,
    [parent_id] [int] NOT NULL)

    I'm trying to construct a single query, which allows me to select an individual's children (if they exist), plus the children's other parent (if it exists). The best I've come up with is below (to explain, i is the original parent, i2 is the child and i3 is the child's other parent. @individualId is the original parent's individual_id)

    SELECT i2.individual_id
    ,i3.individual_id parent_individual_id
    FROM individual i2
    INNER JOIN parent p ON p.individual_id = i2.individual_id --joining to the parent on who this query is based
    INNER JOIN individual i ON p.parent_id = i.individual_id --the parent - @individualId
    LEFT OUTER JOIN parent p2 ON p2.individual_id = i2.individual_id AND p2.is_birth_parent = 1
    LEFT JOIN individual i3 ON p2.parent_id = i3.individual_id AND (i3.individual_id != @individualId)
    WHERE i.individual_id = @individualId

    The problem with this is that where the other parent exists, it brings back two rows per child, one with a correct parent_individual_id, and the other with a null parent_individual_id. In the case that the other parent does not exist, this works fine. I want it to only ever bring back a single row per child.

    Is it possible to perform this double-self-join in a single query?

    Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would simplify my design

    CREATE TABLE [dbo].[individual](
    individual_id int IDENTITY(1,1) NOT NULL)
    mom_id,
    dad_id int NOT NULL)

    both mom and dad as self referencing FKs.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2007
    Posts
    183
    What about siblings?

  4. #4
    Join Date
    Jul 2008
    Posts
    6
    I'd rather not change the nicely normalized database design, as it would impact on updates/deletes, and make the design rather less flexible (eg what if you want an individual to have parents AND adoptive parents?)

    Siblings are implied due to having the same parents. Including sibling relationships as well as parent relationships would introduce unnecessary complexity.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Could you write some DML for sample data? I think I see the solution but can't be bothered generating the data. BTW - I would dump the is_birth_parent column and go for something like parent_type if you want to maintain something that is both flexibile and normalised.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it's so dynamic and flexible he can't code his own solution.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by darasd
    I want it to only ever bring back a single row per child.
    John's parents are Alice and Tom.

    How on earth do you want to show the results?
    Code:
    +-------+--------+
    | child | parent |
    +-------+--------+
    | John  | Alice  |
    | John  | Tom    |
    +-------+--------+
    Deans dad is called Frank, he has no mother

    How do you want the results to appear?
    Code:
    +-------+--------+
    | child | parent |
    +-------+--------+
    | Dean  | Frank  |
    | Dean  | NULL   |
    +-------+--------+
    Your requirements are not clear.
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    it is not possible to not have a mother if you are a mammal.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    No mother on record *
    George
    Home | Blog

  10. #10
    Join Date
    Jul 2008
    Posts
    6
    "I'm trying to construct a single query, which allows me to select an individual's children (if they exist), plus the children's other parent (if it exists)."

    Ignore the is_birth_parent condition in the original Select. This should have been removed...

    SELECT i2.individual_id
    ,i3.individual_id parent_individual_id
    FROM individual i2
    INNER JOIN parent p ON p.individual_id = i2.individual_id --joining to the parent on who this query is based
    INNER JOIN individual i ON p.parent_id = i.individual_id --the parent - @individualId
    LEFT OUTER JOIN parent p2 ON p2.individual_id = i2.individual_id
    LEFT JOIN individual i3 ON p2.parent_id = i3.individual_id AND (i3.individual_id != @individualId)
    WHERE i.individual_id = @individualId

  11. #11
    Join Date
    Jul 2008
    Posts
    6
    Ok, here's some DML that should do the job. I've included new table creation scripts, so everything should be consistant.

    CREATE TABLE [dbo].[individual_test](
    [individual_id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
    CONSTRAINT [PK_individual_test1] PRIMARY KEY CLUSTERED
    (
    [individual_id] ASC
    )
    )
    GO

    CREATE TABLE [dbo].[parent_test](
    [individual_id] [int] NOT NULL,
    [parent_id] [int] NOT NULL,
    CONSTRAINT [PK_parent_test] PRIMARY KEY CLUSTERED
    (
    [individual_id] ASC,
    [parent_id] ASC
    )
    )

    GO
    ALTER TABLE [dbo].[parent_test] WITH CHECK ADD CONSTRAINT [FK_parent_individual_individual_test] FOREIGN KEY([individual_id])
    REFERENCES [dbo].[individual_test] ([individual_id])
    GO
    ALTER TABLE [dbo].[parent_test] CHECK CONSTRAINT [FK_parent_individual_individual_test]
    GO
    ALTER TABLE [dbo].[parent_test] WITH CHECK ADD CONSTRAINT [FK_parent_individual_parent_test] FOREIGN KEY([parent_id])
    REFERENCES [dbo].[individual_test] ([individual_id])
    GO
    ALTER TABLE [dbo].[parent_test] CHECK CONSTRAINT [FK_parent_individual_parent_test]

    insert into individual_test (name)
    select 'anne' union --grandmother, id 1
    select 'bob' union --uncle, id 2
    select 'jane' union --mother (grandmother's daughter), id 3
    select 'john' union --father, id 4
    select 'mary' union --daughter, id 5
    select 'tom' --son, id 6

    insert into parent_test (individual_id, parent_id)
    select 2, 1 union --grandmother is parent of uncle
    select 3, 1 union --grandmother is parent of mother
    select 5, 3 union --mother is parent of daughter
    select 5, 4 union --father is parent of daughter
    select 6, 3 union --mother is parent of son
    select 6, 4 --father is parent of son

    here's my sample select.

    SELECT i2.individual_id
    ,i2.name
    ,i3.individual_id parent_individual_id
    ,i3.name parent_name
    FROM individual_test i2
    INNER JOIN parent_test p ON p.individual_id = i2.individual_id --joining to the parent on who this query is based
    INNER JOIN individual_test i ON p.parent_id = i.individual_id --the parent - @individualId
    LEFT OUTER JOIN parent_test p2 ON p2.individual_id = i2.individual_id
    LEFT JOIN individual_test i3 ON p2.parent_id = i3.individual_id AND (i3.individual_id != @individualId)
    WHERE i.individual_id = @individualId

    Again, I want the Select to return, for a given @individualId, that person's children, plus details of their other parent, if that parent exists. So, for an id of 1 (grandmother), two children rows should be returned with Null parent data. For an id of 3 (mother), two children rows should be returned with the father's parent data.

  12. #12
    Join Date
    Jul 2008
    Posts
    6
    pootle flump, you said you may have a solution...

  13. #13
    Join Date
    Jul 2008
    Posts
    6
    Ok, then, I've come up with a solution, which, whilst avoiding cursors and redesigning my tables, is perhaps somewhat cumbersome. I've created a view of the individual and parent, and a stored procedure which returns the children and the other parent (if it exists) of a given individual.

    The view:
    Code:
    CREATE VIEW [dbo].[vw_parents]
    AS
    SELECT     i.individual_id, i.name, i2.individual_id AS parent_individual_id, i2.name AS parent_name
    FROM         dbo.individual_test AS i INNER JOIN
                          dbo.parent_test AS p ON p.individual_id = i.individual_id INNER JOIN
                          dbo.individual_test AS i2 ON p.parent_id = i2.individual_id
    The stored proc
    Code:
    ALTER PROCEDURE [dbo].[get_children_tree_test]
    	-- Add the parameters for the stored procedure here
    	@individualId	int
    AS
    BEGIN
    	SET NOCOUNT ON;
    
    	DECLARE @n INT;
    
    	SET @n = (SELECT COUNT(*) 
    			    FROM vw_parents v
    			    WHERE v.individual_id IN (SELECT v2.individual_id 
    						         FROM vw_parents v2 
      						         WHERE v2.parent_individual_id = @individualId)
    		                AND v.parent_individual_id != @individualId)
    	
    	IF @n = 0
    		SELECT individual_id
    			  ,name
    			  ,null AS parent_individual_id
    			  ,null AS parent_name
    		  FROM vw_parents v
    		  WHERE v.individual_id IN (SELECT v2.individual_id 
    					       FROM vw_parents v2 
    					       WHERE v2.parent_individual_id = @individualId)
    	ELSE
    		SELECT v.* 
    		  FROM vw_parents v
    		  WHERE v.individual_id IN (SELECT v2.individual_id 
    					       FROM vw_parents v2 
    					      WHERE v2.parent_individual_id = @individualId)
    	               AND v.parent_individual_id != @individualId
    
    END
    Feel free to suggest improvements. I'm frustrated not to be able to achieve the same results with a single select on the base tables.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oooh sorry - I forgot to get back. My initial idea didn't work. I had another idea - not tried yet. Sorry. I'll see if I get a chance tomoz....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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