none
Issue with cross join

    Question

  • Hi Team,

    I am stuck with this query join is based on IDcommon. I have included the expected output according to this it is kind of cross join but i am sure how to get this

    Declare @table Table
    (
    IDCommon int,
    ID1 Int
    )
    
    Insert into @table
    Select 1,10
    Union All
    Select 1,11
    Union All
    Select 1,12
    Union All
    Select 2,30
    Union All
    Select 2,10
    Union All
    Select 3,12
    
    --Expected Ouput it should return all the combinations
    Select  10 FromID,11 ToID
    Union All
    Select  10 FromID,12 ToID
    Union All
    Select  11 FromID,12 ToID
    Union All
    Select  30 FromID,10 ToID
    
    Thanks in advance

    Regards,Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.


    Thursday, September 26, 2013 6:28 AM

Answers

  • Declare @table Table ( IDCommon int, ID1 Int ) Insert into @table Select 1,10 Union All Select 1,11 Union All Select 1,12 Union All Select 2,30 Union All Select 2,10 Union All Select 3,12 ;with cte as ( select ROW_NUMBER()OVER(ORDER BY (SELECT 1)) rown,* from @table a ) SELECT a.id1,b.id1 from cte a , cte b where a.idcommon = b.idcommon and a.id1<>b.id1 and a.rown<b.rown

    /*ouput********

    FromID id1
    10 11
    10 12
    11 12
    30 10

    ***********/



    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog


    Thursday, September 26, 2013 11:01 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. You are still using 1970's Sybase syntax instead of current T-SQL. Since you have no key and no way to have a key you have no table at all! 

    CREATE TABLE Foobar 
    (foo_id INTEGER NOT NULL, 
     bar_id INTEGER NOT NULL, 
     PRIMARY KEY (foo_id, bar_id) --- guess, based on the data!
    );

    INSERT INTO Foobar
    VALUES (1, 10), (1, 11), (1, 12), 
           (2, 30), (2, 10), 
           (3, 12);

    >> Expected Ouput; it should return all the combinations <<

    Within a foo_id? Combinations have no order, but it is handy to impose one 

    ( 10, 11 ) 
    ( 10, 12 ) 
    ( 11, 12 )
    ( 30, 10 )

    SELECT X1.bar_id, X2.bar_id
      FROM Foobar AS X1,Foobar AS X2
     WHERE X1.foo_id = X2.foo_id 
       AND X1.bar_id < X2.bar_id;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 27, 2013 7:59 PM

All replies

  • Try the below:

    Declare @table Table
    (
    IDCommon int,
    ID1 Int
    )
    
    Insert into @table
    Select 1,10
    Union All
    Select 1,11
    Union All
    Select 1,12
    Select * From @table
    
    Select t.ID1,t2.ID1 from @table t
    cross Join @table t2
    Where t.ID1 <> t2.ID1 and t.ID1<t2.ID1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 26, 2013 6:32 AM
  • Thanks Latheesh..

    Forgot to mention it should be joined based on ID. I have updated my actual question.

    select t1.ID1,t2.ID1 from ( select IDCommon, ID1 from @table )t1 full join ( select IDCommon, ID1 from @table )t2 on t2.IDCommon = t1.IDCommon Where t2.ID1 <> t1.ID1 and t1.ID1 <t2.ID1

    ---Modified

    Select t1.ID1,t2.ID1 from 
    @table t1 
    Full Outer Join @table t2 on t1.IDCommon = t2.IDCommon
    Where t2.ID1 <> t1.ID1 and t1.ID1 <t2.ID1

    This works but there may be a performance issue can you help me in rewriting this query? This table will contain millions of rows.

    Regards,Eshwar.


    Please don't forget to Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful. It will helpful to other users.





    Thursday, September 26, 2013 6:36 AM
  • Try this:

    select 
    	t2.ID1,
    	t1.ID1
    from @table t1
      join @table t2
        on
    	  t2.IDCommon = t1.IDCommon
    where 
    	  t1.ID1 <> t2.ID1 
    	and 
    	  t2.ID1 <t1.ID1 ;


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Thursday, September 26, 2013 6:51 AM
  • Declare @table Table ( IDCommon int, ID1 Int ) Insert into @table Select 1,10 Union All Select 1,11 Union All Select 1,12 Union All Select 2,30 Union All Select 2,10 Union All Select 3,12 ;with cte as ( select ROW_NUMBER()OVER(ORDER BY (SELECT 1)) rown,* from @table a ) SELECT a.id1,b.id1 from cte a , cte b where a.idcommon = b.idcommon and a.id1<>b.id1 and a.rown<b.rown

    /*ouput********

    FromID id1
    10 11
    10 12
    11 12
    30 10

    ***********/



    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog


    Thursday, September 26, 2013 11:01 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. You are still using 1970's Sybase syntax instead of current T-SQL. Since you have no key and no way to have a key you have no table at all! 

    CREATE TABLE Foobar 
    (foo_id INTEGER NOT NULL, 
     bar_id INTEGER NOT NULL, 
     PRIMARY KEY (foo_id, bar_id) --- guess, based on the data!
    );

    INSERT INTO Foobar
    VALUES (1, 10), (1, 11), (1, 12), 
           (2, 30), (2, 10), 
           (3, 12);

    >> Expected Ouput; it should return all the combinations <<

    Within a foo_id? Combinations have no order, but it is handy to impose one 

    ( 10, 11 ) 
    ( 10, 12 ) 
    ( 11, 12 )
    ( 30, 10 )

    SELECT X1.bar_id, X2.bar_id
      FROM Foobar AS X1,Foobar AS X2
     WHERE X1.foo_id = X2.foo_id 
       AND X1.bar_id < X2.bar_id;

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 27, 2013 7:59 PM