Change one of your INNER JOINS to a LEFT JOIN. (The comma separated list
of table names is actually a sneaky way to declare INNER JOINS). That way
you will see all of the _objectives records whether or not they appear in
_iso or any of the other tables.
SELECT_objectives.id,
_objectives.name,
COUNT(go._iso._objective_id)
FROM go._objectives
INNER JOIN go._subjectHeadings
ON go._subjectHeadings.id = go._objectives.subjectHeadings_id
INNER JOIN go._subjects
ON go._subjects.id = go._objectives.subjects_id
LEFT JOIN go._iso
ON go._iso._objective_id = _objectives.id
WHERE go._subjectHeadings.id = 276
AND go._subjects.id = 44
GROUP BY 1,2
ORDER BY go._objectives.displayOrder
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Mark Worsdall <[EMAIL PROTECTED]> wrote on 11/10/2004 02:53:29 PM:
> Hi,
>
> The following select returns how many times an id from table _objectives
> is used in table _iso which it does fine but I need it to return
> _objectives.id that are not used in table _iso.
>
> 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
>
>
>
> What I originally had was the following select and then on each record
> returned I did a 2nd select that does a count but this is very slow.
>
>
> 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)
>
>
>
>
> --
> Mark Worsdall
> https://www.paypal.com/refer/pal=LS79YHQ9VUGLJ
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
