Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2015
    Posts
    2

    Unanswered: Help with a query.

    greetings,

    I'm trying to wrap my head around self joins and try to write a query to match my use case. however, I'm only halfway through and need some pointers in the right direction.

    I have a table with parent , child and other attributes. I have another table where parent is associated with some values. My goal is to find each parent's values are matching for each child.
    Code:
     create table ParentChild (
      parentID INTEGER not null,
      childId  INTEGER not null,
      param  varchar(25)
      );
      
      create table ParentXref (
        parentID INTEGER not null,
        value varchar(10)
        );
        
    insert into ParentChild (parentID, childId,param)  values ( 2 , 1, 'parent 2');
    insert into ParentChild (parentID, childId,param) values ( 3 , 1, 'parent 3');
    
    insert into ParentChild (parentID, childId,param)  values ( 4 , 6, 'parent 4');
    insert into ParentChild (parentID, childId,param) values ( 5 , 6, 'parent 5');
    
    
    insert into ParentXref (parentID, value) values (2 , 'USD' );
    insert into ParentXref (parentID, value) values (2 , 'AUD' );
    
    insert into ParentXref (parentID, value) values (3 , 'USD' );
    insert into ParentXref (parentID, value) values (3 , 'AUD' );
    insert into ParentXref (parentID, value) values (3 , 'CHF' );
    insert into ParentXref (parentID, value) values (3 , 'JPY' );
    
    
    insert into ParentXref (parentID, value) values (4 , 'USD' );
    insert into ParentXref (parentID, value) values (4 , 'AUD' );
    
    insert into ParentXref (parentID, value) values (5 , 'USD' );
    insert into ParentXref (parentID, value) values (5 , 'AUD' );
    in this case for child 2 Parents 4 and 5 have exact same values. How do I find such cases for each child. I have the following query so far to identify the child, his parents and parents values.

    Code:
    select parent.childId, parent.parentID,  px.value
    from ParentChild parent,  ParentChild child, ParentXref px 
    where parent.childId = child.childId
    and parent.parentID = px.parentID
    group by  parent.childId, parent.parentID,  px.value
    Edit: my data was incorrect. i have modified it since. In the example above, I have a child 6 with parents (4, 5) both have USD and AUD as values. in the other case child 1 has parents (2,3) which have different values.

    I should get the following result:

    Child|Parent|ParentsMatch
    1 |2 | N
    1 |3 | N
    6 |4 | Y
    6 |5 | Y
    Last edited by cnu; 10-28-15 at 22:29.

  2. #2
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Not sure I follow completely but I give it a try


    select p.childId, p.parentID, p1.value
    from parentchild as p
    LEFT OUTER JOIN parentXref as p1
    on p1.parentid = p.parentid
    Last edited by VLOOKUP; 10-29-15 at 18:21.

  3. #3
    Join Date
    Sep 2010
    Posts
    15
    Do you have a solution yet ?

  4. #4
    Join Date
    Oct 2015
    Posts
    2
    Quote Originally Posted by namman View Post
    Do you have a solution yet ?
    Not yet. I believe I need to write a correlated sub query. Will give it a try today.

    Please suggest if you have any ideas.

    Thanks,

  5. #5
    Join Date
    Sep 2010
    Posts
    15
    In your sample data, parent-child is only 1 level. If your real data is the same, try this query.

    Code:
    ;WITH cte AS
    (
    	SELECT * , r = RANK()  OVER(PARTITION BY pc.childId ORDER BY co.v)
    	FROM ParentChild pc
    	CROSS APPLY
    	(
    		SELECT v = (SELECT ','+value FROM ParentXref p WHERE p.ParentId=pc.parentID ORDER BY value FOR XML PATH(''))
    	)co
    )
    
    SELECT ChildId, parentID , 
    ParentsMatch = IIF(COUNT(*) OVER(PARTITION BY childId) = SUM(r) OVER(PARTITION BY childId),'Y','N')
    FROM cte

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    How much set theory do you remember?

    >> I'm trying to wrap my head around self joins and try to write a query to match my use case. however, I'm only halfway through and need some pointers in the right direction. <<

    The terms “child” and “parent” are not part of RDBMS. They are used in graph theory and old network databases.

    What you seem to want is set-equality tests. You also failed to post valid tables; no keys! Your mindset is still locked into pointer chains, not RDBMS. So you fake the pointers with integers.

    CREATE TABLE Sets
    (set_name CHAR(7) NOT NULL,
    element_name CHAR(3) NOT NULL,
    PRIMARY KEY (set_name, element_name));

    INSERT INTO Sets
    VALUES
    ('alpha', 'USD'), ('alpha', 'AUD'),
    ('beta', 'USD'), ('beta', 'AUD'), ('beta', 'CHF'), ('beta', 'JPY'),
    ('delta', 'USD'), ('delta', 'AUD'),
    ('gamma', 'USD'), ('gamma', 'AUD');

    How much set theory do you remember?

    A = B
    means
    (A  B)  (B  A)

    We can do this in SQL with this query. A 1 means not equal; 0 means the sets are equal.

    WITH
    X1(element_name)
    AS
    ((SELECT element_name FROM Sets WHERE set_name = 'beta')
    EXCEPT
    (SELECT element_name FROM Sets WHERE set_name = 'alpha')),

    X2(element_name)
    AS
    ((SELECT element_name FROM Sets WHERE set_name = 'alpha')
    EXCEPT
    (SELECT element_name FROM Sets WHERE set_name = 'beta')),

    X3 (element_cnt)
    AS
    (SELECT COUNT(*) FROM X1
    UNION
    SELECT COUNT(*) FROM X2)

    SELECT SIGN (SUM(element_cnt)) AS not_equal FROM X3;

    You re-arrange this several other ways

Posting Permissions

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