Counts Puzzle

Question

Post reply

Counts Puzzle

Muhammad Tariq

Ten Centuries

Points: 1020
More actions
August 25, 2008 at 11:43 pm

#191485

Comments posted to this topic are about the item Counts Puzzle
Tariq
master your setup, master yourself.
http://mssqlsolutions.blogspot.com

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

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

ianT SSCrazy Points: 2956 More actions · Answer 1

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: 1209 More actions · Answer 2

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 More actions · Answer 3

ianT

SSCrazy

Points: 2956

August 26, 2008 at 3:10 am

#862972

AAAAAA. Knew there was something I'd missed.

brewmanz SSCommitted Points: 1575 More actions · Answer 4

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 More actions · Answer 5

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: 8156 More actions · Answer 6

There was also that new

grin nullgroup means no one invited

keyword NULLGROUP not far from the DINNER subquery.

Jamie

Chad Crawford SSChampion Points: 11610 More actions · Answer 7

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: 1209 More actions · Answer 8

skyline666

Ten Centuries

Points: 1209

August 26, 2008 at 8:19 am

#863162

Round yours at 6 then 😀

Aleksandr Furman Ten Centuries Points: 1213 More actions · Answer 9

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 More actions · Answer 10

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

skyline666 Ten Centuries Points: 1209 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

Anipaul

SSC-Insane

Points: 24681

August 27, 2008 at 4:44 am

#863605

Good Question...

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 14

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