Results 1 to 5 of 5

Thread: Cross Join

  1. #1
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32

    Unanswered: Cross Join

    I have a more complex requirement of the Cartesian product...
    Im familiar with CROSS JOIN.

    E.g. I want to have all the possible combinations of 2-student groups for a project

    Table_A Grade, Class, Name
    1, 1A, John
    1, 1A, Mike
    1, 1B, Sam
    2, 2A, Pete
    2, 2B, Paul

    SELECT A.Name, B.Name
    FROM Table_A as A
    CROSS JOIN TABLE_B as B


    Result:
    John, Mike
    John, Sam
    John, Pete
    John, Paul
    Mike, Sam
    Mike, Pete
    Mike, Paul
    Sam, Pete
    Sam, Paul
    Etc..

    ***

    So here is what I want. Rather than pair up ALL students, id like to only pair up the ones in the same class.

    Required result:
    John, Mike

    This is the only record being returned since it is the only pair that are in the same class (1A).

    Can I use CROSS JOIN to do this?


    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do it either with a CROSS JOIN --
    Code:
    SELECT a.name
         , b.name
      FROM Table_A AS a
    CROSS 
      JOIN Table_A AS b
     WHERE a.class = b.class
    or with an INNER JOIN --
    Code:
    SELECT a.name
         , b.name
      FROM Table_A AS a
    INNER 
      JOIN Table_A AS b
        ON a.class = b.class
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32
    ive included a.name != b.name to avoid having students paired with themselves e.g. John, John

    SELECT a.name, b.name
    FROM Table_A AS a
    CROSS JOIN Table_A AS b
    WHERE a.class = b.class
    and a.name != b.name


    result:
    Mike, John
    John, Mike

    is there an efficient way to have the query as a combination rather than permutation i.e. Mike, John is the same as John, Mike so the desired result is simply:
    Mike, John

    thanks again
    Last edited by Diesel Dud; 02-27-10 at 12:18.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Diesel Dud View Post
    is there an efficient way to have the query as a combination rather than permutation i.e. Mike, John is the same as John, Mike
    yup --
    Code:
    SELECT a.name
         , b.name
      FROM Table_A AS a
    CROSS
      JOIN Table_A AS b
     WHERE a.class = b.class
       AND a.name < b.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Location
    Montreal, CA
    Posts
    32
    awesome, thanks for the help and the prompt reply

Posting Permissions

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