A simple query needs your help

Question

A simple query needs your help

halifaxdal

SSCoach

Points: 19741
More actions
March 25, 2014 at 10:05 am

#305364

I have two tables to be joined
Doc:
ID, DivID
Division:
ID, Div
CREATE TABLE [dbo].[Doc](
[ID] [int] NULL,
[DivID] [int] NULL
)
CREATE TABLE [dbo].[Division](
[ID] [int] NULL,
[Div] [int] NULL
)
insert into Doc (ID, DivID) Values (1, 1)
insert into Doc (ID, DivID) Values (2, 2)
insert into Doc (ID, DivID) Values (3, 1)
insert into Doc (ID, DivID) Values (4, 3)
insert into Doc (ID, DivID) Values (5, 2)
insert into Doc (ID, DivID) Values (6, 8)
insert into Doc (ID, DivID) Values (7, 7)
insert into Division (ID, Div) Values (1, 'Div1')
insert into Division (ID, Div) Values (2, 'Div2')
insert into Division (ID, Div) Values (3, 'Div3')
insert into Division (ID, Div) Values (4, 'Div4')
insert into Division (ID, Div) Values (5, 'Div5')
insert into Division (ID, Div) Values (6, 'Div6')
insert into Division (ID, Div) Values (7, 'Div7')
insert into Division (ID, Div) Values (8, 'Div8')
As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID
Thank you very much for your help

Viewing 6 posts - 1 through 6 (of 6 total)

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

RTaylor2208 SSChampion Points: 13298 More actions · Answer 1

Can you provide a sample output of what you would expect given your data? I am struggling to understand what data you want returned.

MCITP SQL 2005, MCSA SQL 2012

halifaxdal SSCoach Points: 19741 More actions · Answer 2

RTaylor2208 (3/25/2014)
Can you provide a sample output of what you would expect given your data? I am struggling to understand what data you want returned.

I am looking for an output like:

DivID Total

1 1

2 2

3 0

4 1

5 2

6 0

7 1

8 0

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Is this a good start? Without your expected results based on the sample data it is hard to know what you are actually looking for here.

CREATE TABLE [dbo].[Doc](

[ID] [int] NULL,

[DivID] [int] NULL

)

CREATE TABLE [dbo].[Division](

[ID] [int] NULL,

[Div] varchar(16) NULL

)

insert into Doc (ID, DivID) Values (1, 1)

insert into Doc (ID, DivID) Values (2, 2)

insert into Doc (ID, DivID) Values (3, 1)

insert into Doc (ID, DivID) Values (4, 3)

insert into Doc (ID, DivID) Values (5, 2)

insert into Doc (ID, DivID) Values (6, 8)

insert into Doc (ID, DivID) Values (7, 7)

insert into Division (ID, Div) Values (1, 'Div1')

insert into Division (ID, Div) Values (2, 'Div2')

insert into Division (ID, Div) Values (3, 'Div3')

insert into Division (ID, Div) Values (4, 'Div4')

insert into Division (ID, Div) Values (5, 'Div5')

insert into Division (ID, Div) Values (6, 'Div6')

insert into Division (ID, Div) Values (7, 'Div7')

insert into Division (ID, Div) Values (8, 'Div8')

select

*

from

dbo.Division d1

left outer join dbo.Doc d2

on (d1.ID = d2.DivID)

go

drop table dbo.Doc;

drop table dbo.Division;

dbalmf SSCrazy Points: 2063 More actions · Answer 4

Hi,

My Take on it.. 🙂

WITH DocCTE (DivIDCte, DivIDCount)

AS

(SELECT DivID,

COUNT(DivID)

FROM Doc GROUP BY DivID)

SELECTID,

Div,

'Count' = ISNULL(DivIDCount,0)

FROM Division d

LEFT OUTER JOIN DocCTE dc ON dc.DivIDCte = d.ID

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

halifaxdal SSCoach Points: 19741 More actions · Answer 5

dbalmf (3/25/2014)
Hi,
My Take on it.. 🙂
WITH DocCTE (DivIDCte, DivIDCount)
AS
(SELECT DivID,
COUNT(DivID)
FROM Doc GROUP BY DivID)
SELECTID,
Div,
'Count' = ISNULL(DivIDCount,0)
FROM Division d
LEFT OUTER JOIN DocCTE dc ON dc.DivIDCte = d.ID

Thank you and Lynn very much, this is what I need