Counts Puzzle

  • Muhammad Tariq

    Ten Centuries

    Points: 1020

    Comments posted to this topic are about the item Counts Puzzle

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

  • ianT

    SSCrazy

    Points: 2956

    Shouldnt this be

    SELECT C.CATID, COUNT(*)

    FROM #DATA DINNER

    JOIN #CATEGORY C ON (C.VAL1 = DINNER.VAL1) OR (C.VAL1 IS NULL)

    GROUP BY C.CATID

    (was ON C.VAL1 = D.VAL1)

  • skyline666

    Ten Centuries

    Points: 1099

    ian treasure (8/26/2008)


    Shouldnt this be

    SELECT C.CATID, COUNT(*)

    FROM #DATA DINNER

    JOIN #CATEGORY C ON (C.VAL1 = DINNER.VAL1) OR (C.VAL1 IS NULL)

    GROUP BY C.CATID

    (was ON C.VAL1 = D.VAL1)

    Nope, its this:

    SELECT C.CATID, COUNT(*)

    FROM #DATA D INNER JOIN

    #CATEGORY C ON (C.VAL1 = DVAL1) OR (C.VAL1 IS NULL)

    GROUP BY C.CATID

  • ianT

    SSCrazy

    Points: 2956

    AAAAAA. Knew there was something I'd missed.

  • brewmanz

    SSCommitted

    Points: 1574

    ian treasure (8/26/2008)


    Shouldnt this be

    SELECT C.CATID, COUNT(*)

    FROM #DATA DINNER

    JOIN #CATEGORY C ON (C.VAL1 = DINNER.VAL1) OR (C.VAL1 IS NULL)

    GROUP BY C.CATID

    (was ON C.VAL1 = D.VAL1)

    You must be like me ... always thinking of food πŸ˜‰

  • Muhammad Tariq

    Ten Centuries

    Points: 1020

    hey,

    here is the details,

    1) inner join or join, its behavior is same, so there will be no effect if you replace inner join with join.

    2) parenthesis will not change its behavior, because we have only two conditions in where clause to check, parenthesis will play role when you will have multiple combination of OR and AND operators.

    so, i believe code is correct, πŸ™‚

    Tariq
    master your setup, master yourself.
    http://mssqlsolutions.blogspot.com

  • Jamie-2229

    SSCrazy Eights

    Points: 8151

    There was also that new

    grin nullgroup means no one invited

    keyword NULLGROUP not far from the DINNER subquery.

    Jamie

  • Chad Crawford

    SSChampion

    Points: 11605

    mmmmm.... Dinner Join.... sounds like an invitation to a barbeque.

    Reminds me of the QOD from 12/13/07 that used a MIDDLE JOIN. πŸ™‚

    Chad

  • skyline666

    Ten Centuries

    Points: 1099

    Round yours at 6 then πŸ˜€

  • Aleksandr Furman

    SSC Eights!

    Points: 916

    skyline666 (8/26/2008)


    ian treasure (8/26/2008)


    Shouldnt this be

    SELECT C.CATID, COUNT(*)

    FROM #DATA DINNER

    JOIN #CATEGORY C ON (C.VAL1 = DINNER.VAL1) OR (C.VAL1 IS NULL)

    GROUP BY C.CATID

    (was ON C.VAL1 = D.VAL1)

    Nope, its this:

    SELECT C.CATID, COUNT(*)

    FROM #DATA D INNER JOIN

    #CATEGORY C ON (C.VAL1 = DVAL1) OR (C.VAL1 IS NULL)

    GROUP BY C.CATID

    When you're copying the code to query window remember to add proper line brakes so you don't end up with "DINER Join" :w00t: . Must've been hungry? Thanks for the laughs :hehe:

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

    You forgot to put drops for you temp tables (#data, #category) at the end of the code block.

  • skyline666

    Ten Centuries

    Points: 1099

    YeshuaAgapao (8/26/2008)


    You forgot to put drops for you temp tables (#data, #category) at the end of the code block.

    That doesn't affect the end result tho ;).

  • YeshuaAgapao

    Hall of Fame

    Points: 3495

    It don't, but you wouldn't want to see this coding practice (not dropping temp tables) in real applications, so one would want to set an example for that for noobs and semi-noobs that are still moldable.

    Better yet, re-write it so it uses table variables.

  • Anipaul

    SSC-Insane

    Points: 24681

    Good Question...

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    YeshuaAgapao (8/26/2008)


    It don't, but you wouldn't want to see this coding practice (not dropping temp tables) in real applications, so one would want to set an example for that for noobs and semi-noobs that are still moldable.

    Better yet, re-write it so it uses table variables.

    I think it is perfectly valid not to drop temp tables explicitly, since they go out of scope automatically.

    I am also curious to know why you favor table variables, especially for this example.

    Best Regards,

    Chris BΓΌttner

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

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