SQL puzzle

  • Hi,

    Can anyone solve this puzzle ?

    Two tables Emp1 and Emp2

     

    Emp1                     Emp2

    101                       101

    102                       102

    103                       103

    104                        105

     

    The final result should be

    104

    105

    Good LUCK!!!

    --Sandeep

     

     

  • I can.

    And have solved this kind of tasks many times.

    Is it your homework?

    So, you have to do it.

    _____________
    Code for TallyGenerator

  • There are at least three SET-BASED methods you can use

    1) FULL JOIN

    2) UNION ALL with GROUP BY

    3) UNION ALL with LEFT JOIN

     


    N 56°04'39.16"
    E 12°55'05.25"

  • -- Prepare sample data

    DECLARE @Emp1 TABLE (Emp INT)

    INSERT @Emp1

    SELECT 101 UNION ALL

    SELECT 102 UNION ALL

    SELECT 103 UNION ALL

    SELECT 104

    DECLARE @Emp2 TABLE (Emp INT)

    INSERT @Emp2

    SELECT 101 UNION ALL

    SELECT 102 UNION ALL

    SELECT 103 UNION ALL

    SELECT 105

    -- Show the expected output 1

    SELECT          COALESCE(e1.Emp, e2.Emp)

    FROM            @Emp1 AS e1

    FULL JOIN       @Emp2 AS e2 ON e2.Emp = e1.Emp

    WHERE           e1.Emp IS NULL

                    OR e2.Emp IS NULL

    -- Show the expected output 2

    SELECT          Emp

    FROM            (

                            SELECT  Emp

                            FROM    @Emp1

                            UNION ALL

                            SELECT  Emp

                            FROM    @Emp2

                    ) AS x

    GROUP BY        Emp

    HAVING          COUNT(*) = 1

    -- Show the expected output 3

    SELECT          e1.Emp

    FROM            @Emp1 AS e1

    LEFT JOIN       @Emp2 AS e2 ON e2.Emp = e1.Emp

    WHERE           e2.Emp IS NULL

    UNION ALL

    SELECT          e2.Emp

    FROM            @Emp2 AS e2

    LEFT JOIN       @Emp1 AS e1 ON e1.Emp = e2.Emp

    WHERE           e1.Emp IS NULL

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Did you just do someone's homework, Peter?  Hope you get an "A"

    --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)

  • Hey, you know me...

    At least I didn't write

    sp_msforeachdb 'drop database ''?''' or similar.

     


    N 56°04'39.16"
    E 12°55'05.25"

  • He does not need your help on it.

    Such a "brilliant" SQL developer will kill all projects without anyone's help.

    _____________
    Code for TallyGenerator

  • His professor will notice that the code is not written by him.

    It is out of his style, if he needs to question for help.

    I know some of my occasional students cheat, just by reading their suggestions to the problem I give them.

     


    N 56°04'39.16"
    E 12°55'05.25"

  • So, whom did you help?

    _____________
    Code for TallyGenerator

  • Oooohhh.... now that's just plain evil   Hadn't thought about doing something that nasty

    As for professors catching code "out of a student's style"?...  According to the level of idiocy of I've experienced from people interviewing for SQL jobs, most instructors aren't catching things like this.

    --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)

  • Since the only requirement posted was the output, this should do it.

    select 104 union all select 105 order by 1

    Good LUCK!!!

     

  • Hi Peter and Micheal,

    Thank you very much for your extended help .

     

    as for "SERIGY" and "JEFF MODEN" .....i am a SQL amateur and just thought of a possibility. This ain't a homework for me !!

    Also i suppose that this forum is for helping others and not to challenge anyone's capabilty. You never know what "someone" can become.

    If you people got those guts....u participate in SQL contests and compete with those above you and not those who are amateur.

    At the same time sorry for being candid but i felt really bad looking at your replies !!!

  • Sandeep, if it was not your homework you could explain what do you need it for.

    It does not look like real life task, it's more like exercise from a schoolbook.

    _____________
    Code for TallyGenerator

  • Select * from emp1 full outer join emp2 on emp1.empno=emp2.empno

    where emp1.empno is null or emp2.empno is null

  • Use the ANSI standard EXCEPT operator, which gives you the difference between two sets. It also exists in SQL Server 2005, and in Oracle, it is called "MINUS".

Viewing 15 posts - 1 through 15 (of 19 total)

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