Post reply

TSQL Help

  • February 17, 2011 at 2:07 pm

    #246895

    Hi Friends

    i have a table

    student_id School_Id

    100 1

    100 2

    100 3

    my query has to select the records like this

    studentid ori dup

    100 1 2

    100 1 3

    100 2 1

    100 2 3

    100 3 1

    100 3 2

    here the permutations are not constant we may have multiple combinations

    Thanks In Advance

  • February 17, 2011 at 2:09 pm

    #1288516

    Looks like homework. Or the first question on an interview tech screening.

    Hint: Join the table to itself.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 17, 2011 at 2:28 pm

    #1288530

    No No its not home work or technical screening? can you please provide any

    resource for solving this.

  • February 17, 2011 at 2:30 pm

    #1288533

    Do you know how to query a table and join it to another table?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • February 17, 2011 at 7:28 pm

    #1288606

    As GSquared hinted, this looks like a homework..But as u are insisting, i am providing a solution.

    declare @tab table

    ( student_id int,

    School_Id int

    )

    insert into @tab

    select 100 , 1

    union all select 100 , 2

    union all select 100 , 3

    select T1.student_id , t1.School_Id , t2.School_Id

    from @tab t1

    cross join @tab t2

    where t1.School_Id <> t2.School_Id

    order by t1.School_Id , t2.School_Id

    Hope this is not home work.

  • February 17, 2011 at 7:31 pm

    #1288608

    Partial self-referencing Cross-Join disguised as an inner join with duplicate exclusion... you'll need to change the table names to your real table name.

    SELECT orig.Student_ID,

    Orig = orig.School_ID,

    Dupe = dupe.School_ID

    FROM #TestTable orig

    INNER JOIN #TestTable dupe

    ON orig.Student_ID = dupe.Student_ID

    AND orig.School_ID <> dupe.School_ID

    ORDER BY orig.Student_ID, Orig

    For those that want to play, here's the test data I used...

    SELECT *

    INTO #TestTable

    FROM

    (

    SELECT 100, 1 UNION ALL

    SELECT 100, 2 UNION ALL

    SELECT 100, 3 UNION ALL

    SELECT 200, 1 UNION ALL

    SELECT 200, 2 UNION ALL

    SELECT 200, 3 UNION ALL

    SELECT 200, 4 UNION ALL

    SELECT 200, 5 UNION ALL

    SELECT 200, 6

    ) testdata (Student_ID, School_ID)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 17, 2011 at 7:32 pm

    #1288609

    Heh... dang it... CC beat me this time. 😀

    CC... try your code against the data I posted. You may have a surprise in store.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 17, 2011 at 7:38 pm

    #1288611

    Actually, these are "double Triangular Joins". Please see the following article for the different types of "square" and "triangular joins".

    http://qa.sqlservercentral.com/articles/T-SQL/61539/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 17, 2011 at 7:44 pm

    #1288612

    Jeff Moden (2/17/2011)

    CC... try your code against the data I posted. You may have a surprise in store.

    Oh yeah, now i get it , Jeff.. completely missed it ; just cooked a code a for the OP's data 🙁 Thanks for pointing it out Jeff...

  • February 17, 2011 at 9:16 pm

    #1288632

    ColdCoffee (2/17/2011)

    Jeff Moden (2/17/2011)

    CC... try your code against the data I posted. You may have a surprise in store.

    Oh yeah, now i get it , Jeff.. completely missed it ; just cooked a code a for the OP's data 🙁 Thanks for pointing it out Jeff...

    You bet... thanks for the feedback. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply