COUNT WITH MULTIPLE JOINS 1

[slowfish]

Hi

I'm trying to count the total number of workdays for all volunteers that are not "Type 4" volunteers. A volunteer can be multiple types, hence I think why I'm getting a much larger count than expected.

SELECT COUNT(taskvolsnew.vol) AS howmany

FROM taskvolsnew

LEFT JOIN tasks
ON taskvolsnew.taskref = tasks.ref
AND taskvolsnew.taskregion = tasks.region

LEFT JOIN volunteers_types
ON taskvolsnew.vol = volunteers_types.volref
AND taskvolsnew.volregion = volunteers_types.volregion
AND volunteers_types.voltype != '4'

Any help greatly appreciated.

 

[r937]

try this --

SELECT COUNT(volunteers_types.volref) AS howmany
  FROM taskvolsnew
LEFT OUTER
  JOIN volunteers_types
    ON volunteers_types.volref = taskvolsnew.vol
   AND volunteers_types.volregion = taskvolsnew.volregion
   AND volunteers_types.voltype <> '4'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

Thanks for that Rudy.

It still doesn't return the correct count though, I think maybe something to do with the fact I'm also joining the tasks table.

 

[r937]

yeah, but, see, that's why i took the join to the tasks table out of the query -- it's producing the wrong count





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

It still doesn't return the correct count even if the join to the tasks table is taken out. A volunteer can be multiple "types", so for a volunteer that is not type '4' but is type 3, 6 and 9 for example it is counting them 3 times, rather than once.

 

[r937]

okay, try this --

SELECT COUNT(volunteers_types.volref) AS howmany  
  FROM taskvolsnew
LEFT OUTER
  JOIN volunteers_types
    ON volunteers_types.volref = taskvolsnew.vol
   AND volunteers_types.volregion = taskvolsnew.volregion
   AND volunteers_types.voltype [blue]= '4'
 WHERE volunteers_types.voltype IS NULL[/blue]

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

that just returns a count of zero

 

[r937]

well, i guess in order to come up with the right SQL, i'm gonna have to stop guessing, and actually get to know the relationships between your tables

what's a workday? what are the three tables for? how are they related?


r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

really appreciate the help rudy...

"tasks" table has date of the task along with various other info (site, leader, tasktype, etc, etc)

"taskvolsnew" table contains id's of all the vols that were on a particular task (multiple vols on a task)

"volunteers_types" table contains the voltype id's of all the vols (each vol can be multiple voltypes)

A workday is one vol on a task. Ten vols on the task, 10 workdays, etc.

How many vol workdays of those vols who are NOT voltype 4?

 

[r937]

since each vol can be multiple types, i think i'm lost

how do you count a vol who has two types on the same task?

what is the context for "vols who are NOT voltype 4" ??

on that task? or ever?




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

it's irrelevant how many types the vol is - whenever they are on task it should just be a count of one.

need to exclude voltype 4 vols from the count for all tasks they have ever been on

 

[r937]

SELECT COUNT(*) AS howmany
  FROM taskvolsnew
 WHERE vol NOT IN
       ( SELECT volref
           FROM volunteers_types
            AND voltype = '4' )

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[slowfish]

Great thanks for that, it worked. Just had to replace the AND with WHERE.

SELECT COUNT(*) AS howmany
  FROM taskvolsnew
 WHERE vol NOT IN
       ( SELECT volref
           FROM volunteers_types
            WHERE voltype = '4' )

 

[r937]

yeah, sorry, copy/paste error ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon