T SQL

  • Mark Pratt (5/27/2009)


    I don't look at these challenges much. Today's item doesn't encourage me to do so in the future.

    Talk about quick to judge... One typo'd question and the whole of QOD is written off. That seems a little harsh to me, considering the number of interesting little snippets I've picked up from the various QOD's over the last few months.

  • Rachel Byford (5/27/2009)


    What a lot of complainers!

    If everybody who had posted came up with a question of their own I bet we'd have some really good questions. And they'd all be perfect of course!

    🙂

    Hear, Hear!

    I always look forward to the QOD, even when I guess incorrectly.

  • Yup, I also ran it in SQL and got:

    row1 - Vyas Mohan

    row2 - Mohan Shobha

    row3 - Shridhar Mohan

    row4 - Sourabh Mohan

    So I just tried to pick an answer that was close so I could see he discussion. Is there a specific way to approach this in order to get the listed answer?

  • Laurie Dunn (5/27/2009)


    Yup, I also ran it in SQL and got:

    row1 - Vyas Mohan

    row2 - Mohan Shobha

    row3 - Shridhar Mohan

    row4 - Sourabh Mohan

    So I just tried to pick an answer that was close so I could see he discussion. Is there a specific way to approach this in order to get the listed answer?

    Yup, don't forget the ORDER BY. The email that went out was lacking it. So if you answered based on the email you get the above order, else you would get the correct answer per the question.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • The corrected question, which is on the web site, is

    CREATE TABLE E (eid int, mid int, en char(10) )

    INSERT E SELECT 1,2,'Vyas'

    INSERT E SELECT 2,3,'Mohan'

    INSERT E SELECT 3,NULL,'Shobha'

    INSERT E SELECT 4,2,'Shridhar'

    INSERT E SELECT 5,2,'Sourabh'

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    order by t1.en

    There is no way you can get "Vyas Mohan" first.

    Running the SQL from the newsletter kind of defeats the purpose in answering. The idea is for you to use your knowledge to figure it out.

  • Goes to prove that you get hanged by the same clever rope you wove.

  • Argh! The question changed from the email and I didn't read the web page's version of the question!

  • Steve Jones - Editor (5/27/2009)


    The corrected question, which is on the web site, is

    CREATE TABLE E (eid int, mid int, en char(10) )

    INSERT E SELECT 1,2,'Vyas'

    INSERT E SELECT 2,3,'Mohan'

    INSERT E SELECT 3,NULL,'Shobha'

    INSERT E SELECT 4,2,'Shridhar'

    INSERT E SELECT 5,2,'Sourabh'

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    order by t1.en

    There is no way you can get "Vyas Mohan" first.

    Running the SQL from the newsletter kind of defeats the purpose in answering. The idea is for you to use your knowledge to figure it out.

    Sure you can... read it from top down without the ORDER BY.

    eid 1 gives you Vyas which joins to eid 2 and gets Mohan

    Vyas Mohan

    followed by Mohan eid 2 joining to eid 3 and getting

    Mohan Shobha

    if you read the email, figured out what you were doing, and then clicked the link, and answered the question (because you didn't see the order by.. on my machine it was hidden by a scrollbar at first), you'd easily get Vyas Mohan first.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • You're right, my response was somewhat whiny given that the original post was most likely a keyboard error, not a logic error. I'll save my criticisms for when I've accumulated enough points to be classified something beyond Grasshopper.

  • I typically read the email, solve the problem in my head, click the link and select the answer I got. Now you're saying I have to ignore the email because what is in there doesn't reflect the actual question? Sounds like a weasel answer to me. Just admit the question was not properly edited in both the content or the alternative presentations and move on.

  • Barry McConnell (5/27/2009)


    I typically read the email, solve the problem in my head, click the link and select the answer I got.

    That's my MO, too. I don't expect there to be a difference between the newsletter and the website.

  • WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.

    SQL DBA.

  • Question

    Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    order by t1.en

    SELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid

    order by t1.en

  • SanjayAttray (5/27/2009)


    WOW.........never seen so many complaints for a single question on QOD before. Good job Reddy. You made all forum posters stand united.

    You must not have seen the MDX Except question then! :w00t:

  • cj_logan (5/27/2009)


    Question

    Why is the inner join the recommended option ? I know Microsoft recommends this way but no where can I find a reason ? According to the execution plan both are the same and both cost 50%

    SELECT t1.en [Em], t2.en [Ma] FROM E t1, E t2 WHERE t1.mid = t2.eid

    order by t1.en

    SELECT t1.en [Em], t2.en [Ma] FROM E t1 inner join E t2 on t1.mid = t2.eid

    order by t1.en

    Because that is part of the ANSI standard for SQL. The older syntax should be phased out. The argument goes that the new syntax is cleaner by separating the JOIN logic from the filter (WHERE) logic.

Viewing 15 posts - 46 through 60 (of 74 total)

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