Can't get count(go._iso._objective_id) to return 0 values

[Mark Worsdall]

I am trying to merge the following 2 selects into one select.
!!! Returns all custom objectives. Another check is needed to
see if in use. !!!
SELECT
_objectives.id,
_objectives.subjects_id,
_objectives.subjectHeadings_id,
_objectives.name,
_objectives.active,
_objectives.displayOrder
FROM
go._objectives, go._subjectHeadings, go._subjects
WHERE
go._subjectHeadings.id = 276
AND
go._subjects.id = 44
AND
go._subjectHeadings.id = go._objectives.subjectHeadings_id
AND
go._subjects.id = go._objectives.subjects_id
ORDER BY go._objectives.displayOrder
This returns 58 records.
I then on each record do the following to determine if this id is in 
use.

SELECT
COUNT(*)
FROM go._iso, go._ltaForm
WHERE
_objective_id = $objectiveID AND _ltaForm.id = _iso.ltaForm_id
($objectiveID = $row[0] from 1st query)
Now as you can imagine this really bashes the server once records get 
above 200.

I tried the following which returns 55 records that are in use and the 
count bit tells me how many times each id is in use but it does not tell 
me what id's are not in use.

!!! Works but does not return any custom objectives that are not in use 
!!!
SELECT
_objectives.id,
_objectives.name,
COUNT(go._iso._objective_id)
FROM
go._objectives, go._subjectHeadings, go._subjects, go._iso
WHERE
go._subjectHeadings.id = 276
AND
go._subjects.id = 44
AND
go._subjectHeadings.id = go._objectives.subjectHeadings_id
AND
go._subjects.id = go._objectives.subjects_id
AND
go._iso._objective_id = _objectives.id
GROUP BY go._iso._objective_id
ORDER BY go._objectives.displayOrder

RETURNS 55 records
So it needs to return the 3 other missing records with a count value of 
0.

Any ideas?
If there is a better place for this query, please tell.
M.
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]