# Counts Puzzle

Ten Centuries

Points: 1020

Tariq
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: 1575

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 😉

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

SSChampion

Points: 11610

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

Reminds me of the QOD from 12/13/07 that used a MIDDLE JOIN. 🙂

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