Counting and Joining into one recordset

[emozley]

I have written a small app that lets the user create records in a database of a file's location, what department in the company it is relevant to, what category it is in within that and also what subcategory within that.

I have three tables called DocsDept, DocsCat and DocsSubCat. These three tables each have a uniqueID column and a text column.

There is a 4th table called DocsMaster that has (amongst others) columns called Department (matches the unique ID field in DocsDept), Category (same for DocsCat) and SubCategory (same for DocsSubCat).

I have written a page (ASP) that builds a sort of map with all the departments, categories and subcategories. To do this I used the following query:

SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat
FROM (DocsDept
LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept)
LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat
ORDER BY DocsCat.DocsCat ASC, DocsSubCat.DocsSubCat ASC

Is there anyway of counting the number of records in my DocsMaster table that have the particular department, category and subcategory for each row in the recordset?

Thanks very much

Ed

 

[rac2]

SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat[b][COLOR=orange],

COUNT(*) AS "Number of Documents"[/color][/b]

FROM (DocsDept
LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept)
LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat
[b][COLOR=orange]
LEFT JOIN DocsMaster ON DocsMaster.DocsSubCatID = DocsSubCat.DocsSubCatID 
      AND DocsMaster.DocsCatID = DocsCat.DocsCatID 
      AND DocsMaster.DocsDeptID = DocsDept.DocsDeptID


GROUP BY DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat
[/color][/b]
ORDER BY DocsCat.DocsCat ASC, DocsSubCat.DocsSubCat ASC

 

[emozley]

Hi,

Sorry for the slow reply - I've tried it and but got an error message. I tried putting in some extra brackets but now it says the join is not supported:

SELECT DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat,

COUNT(*) AS "Number of Documents"

FROM (((DocsDept
LEFT JOIN DocsCat ON DocsDept.DocsDeptID=DocsCat.DocsDept)
LEFT JOIN DocsSubCat ON DocsCat.DocsCatID=DocsSubCat.DocsCat)

LEFT JOIN DocsMaster ON DocsMaster.DocsSubCatID = DocsSubCat.DocsSubCatID
AND DocsMaster.DocsCatID = DocsCat.DocsCatID
AND DocsMaster.DocsDeptID = DocsDept.DocsDeptID)


GROUP BY DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat, DocsSubCat.DocsSubCatID, DocsSubCat

ORDER BY DocsCat.DocsCat ASC, DocsSubCat.DocsSubCat ASC

 

[Golom]

Since you are not using any fields from "DocsMaster" in your SELECT, this should be equivalent

SELECT D.DocsDeptID, D.DocsDept, C.DocsCatID, C.DocsCat, 
S.DocsSubCatID, S.DocsSubCat, COUNT(*) AS [Number of Documents]

FROM (DocsDept D
      LEFT JOIN DocsCat    C ON D.DocsDeptID = C.DocsDept)
      LEFT JOIN DocsSubCat S ON C.DocsCatID  = S.DocsCat

WHERE EXISTS (Select 1 From DocsMaster As M
              Where M.DocsSubCatID = S.DocsSubCatID 
                AND M.DocsCatID    = C.DocsCatID 
                AND M.DocsDeptID   = D.DocsDeptID )

GROUP BY D.DocsDeptID, D.DocsDept, C.DocsCatID, 
         C.DocsCat, S.DocsSubCatID, S.DocsSubCat

ORDER BY C.DocsCat ASC, S.DocsSubCat ASC

 

[emozley]

Hmmm... still not having any luck. I am going to go back to basics and try and make a list that has the DocsDeptID (number) and the DocsDept (text) and also the number of records in the DocsMaster table where DocsMaster.Department=DocsDept.DocsDeptID

SELECT DocsDept.DocsDeptID, DocsDept.DocsDept, COUNT(*) As DeptTotal FROM DocsMaster WHERE DocsMaster.Department=DocsDept.DocsDeptID
FROM DocsDept;

I am getting a missing operator error message though when I try to do this. Do I need to do a join of some kind?

Thanks very much

Ed

 

[Golom]

That won't work because you have two FROM clauses. Perhaps something like

SELECT DocsDept.DocsDeptID, DocsDept.DocsDept, COUNT(*) As DeptTotal 

FROM DocsMaster INNER JOIN DocsDept 
     ON DocsMaster.Department=DocsDept.DocsDeptID

 

[Golom]

Oops ... you also need a GROUP BY

SELECT DocsDept.DocsDeptID, DocsDept.DocsDept, COUNT(*) As DeptTotal 

FROM DocsMaster INNER JOIN DocsDept 
     ON DocsMaster.Department=DocsDept.DocsDeptID

Group By DocsDept.DocsDeptID, DocsDept.DocsDept

 

[emozley]

Excellent! Now if I want to have three extra columns - DocsCat.DocsCatID, DocsCat.DocsCat and also the total number of records from DocsMaster WHERE DocsMaster.Category=DocsCat.DocsCatID...

So far I have the following but it kicks up an error about an aggregate function - is this because I've not grouped at the end properly?

Thanks very much!

SELECT DocsDept.DocsDeptID, DocsDept.DocsDept, COUNT(*) As DeptTotal, DocsCat.DocsCatID, DocsCat.DocsCat, COUNT(*) As CatTotal

FROM (DocsMaster INNER JOIN DocsDept
ON DocsMaster.Department=DocsDept.DocsDeptID)

INNER JOIN DocsCat ON DocsMaster.Category=DocsCat.DocsCatID

Group By DocsDept.DocsDeptID, DocsDept.DocsDept

 

[lespaul]

that's right, every field in the select that is not an aggregate (COUNT, SUM, etc) has to be listed in the group by:

SELECT DocsDept.DocsDeptID, DocsDept.DocsDept, COUNT(*) As DeptTotal, DocsCat.DocsCatID, DocsCat.DocsCat, COUNT(*) As CatTotal

FROM (DocsMaster INNER JOIN DocsDept
     ON DocsMaster.Department=DocsDept.DocsDeptID)

     INNER JOIN DocsCat ON DocsMaster.Category=DocsCat.DocsCatID

Group By DocsDept.DocsDeptID, DocsDept.DocsDept, DocsCat.DocsCatID, DocsCat.DocsCat

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[emozley]

I see!

The results that the query gives are quite interesting. Whereas in the first basic one it would give a total for the Department but with the new query the value in DeptTotal is the same as CatTotal.

Ideally I might want part of the recordset to look something like:

DeptTotal CatTotal

15 6
15 6
15 3

Is this possible or would I be better off manipulating the data in ASP once I've got the recordset?

Thanks again

E.

 

[PHV]

Is there anyway of counting the number of records in my DocsMaster table that have the particular department, category and subcategory for each row in the recordset?
Perhaps this ?
SELECT D.DocsDeptID, D.DocsDept, C.DocsCatID, C.DocsCat, S.DocsSubCatID, S.DocsSubCat
, (SELECT Count(*) FROM DocsMaster AS M WHERE M.Department = D.DocsDeptID
AND M.Category = C.DocsCatID AND M.SubCategory = S.DocsSubCatID) AS CountOfDocs
FROM (DocsDept AS D
LEFT JOIN DocsCat AS C ON D.DocsDeptID=C.DocsDept)
LEFT JOIN DocsSubCat AS S ON C.DocsCatID=S.DocsCat
ORDER BY 4, 6

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886