#1
  1. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533

    Select all sibling records


    I have the following three tables.

    Code:
    super
    -id (PK)
    -data
    
    parent
    -id (PK, FK references super.id)
    -data
    
    child
    -id (PK, FK references super.id)
    -parent_id (FK references parent.id)
    -data
    Given child.id, how should I select all other child records child records that have the same parent? I am thinking something like the following, but would like a second opinion. Thank you

    Code:
    SELECT 
    FROM child AS child1
    INNER JOIN child AS child2 ON child2.parent_id=child1.parent_id
    INNER JOIN parent ON parent.id=child1.parent_id
    INNER JOIN super ON super.id=child2.id
    WHERE child1.id=123
    AND child2.id != 123;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    SELECT sibs.name
      FROM child
    INNER
      JOIN child AS sibs
        ON sibs.id = child.id
       AND sibs.parent_id = child.parent.id
       AND sibs.data <> child.data
     WHERE child.id = 123
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Sep 2006
    Posts
    1,916
    Rep Power
    533
    Thank you Rudy,

    Similar query with a couple of differences.

    You put sibs.data <> child.data in the ON clause where I put child2.id != 123 in the WHERE clause. Is there benefits putting it one location over the other? Do you recommend using <> over !=?

    What is the purpose of sibs.id = child.id in the ON clause?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by NotionCommotion
    You put sibs.data <> child.data in the ON clause where I put child2.id != 123 in the WHERE clause. Is there benefits putting it one location over the other?
    yes, because it makes a huge difference in outer joins

    Originally Posted by NotionCommotion
    Do you recommend using <> over !=?
    absolutely, yes, because only one of them is standard sql

    Originally Posted by NotionCommotion
    What is the purpose of sibs.id = child.id in the ON clause?
    yes, i guess that's redundant

    Comments on this post

    • NotionCommotion agrees : Good point about the outer joins!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo