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
58 rows in set (24.26 sec)
Did what I wanted but took as long as old method.
the _iso.user_id value for this is 175 in case that can be used to speed
things up.
I have tried various things but they all end up with the servers hard
drive going nutty and /tmp (1Gb) filling up!!!
I have included the descriptions of the tables and a count on them to
show number of records involved.
I can't see the solution yet but I am sure it can be done with the
select without me delving into indexes etc.
describe _objectives;
+--------------------+---------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+--------------------+---------------+------+-----+---------+
| id | int(10) | | PRI | NULL |
| subjects_id | int(10) | YES | | NULL |
| subjectHeadings_id | int(10) | YES | | NULL |
| name | varchar(240) | | | |
| active | enum('Y','N') | | | Y |
| displayOrder | int(10) | | | 10 |
| owner_id | int(10) | | | 0 |
+--------------------+---------------+------+-----+---------+
SELECT count(*) from go._objectives;
+----------+
| count(*) |
+----------+
| 4087 |
+----------+
1 row in set (0.01 sec)
describe _iso;
+---------------+---------------+------+-----+---------------------+
| Field | Type | Null | Key | Default |
+---------------+---------------+------+-----+---------------------+
| id | int(10) | | PRI | NULL |
| student_id | int(10) | | | 0 |
| ltaForm_id | int(10) | | | 0 |
| lta_id | int(10) | | | 0 |
| _objective_id | int(10) | | | 0 |
| sta | varchar(254) | | | |
| comment | varchar(254) | | | |
| lecturerNotes | varchar(254) | | | |
| displayOrder | int(10) | | | 100 |
| manstatus | enum('W','C') | | | W |
| autostatus | enum('W','C') | | | W |
| recType | enum('T','L') | | | T |
| active | enum('Y','N') | | | Y |
| create_date | datetime | | | 2003-12-08 00:00:00 |
| modified_date | timestamp(14) | YES | | NULL |
| moduser_id | int(10) | | | 0 |
| user_id | int(10) | | | 0 |
| recuser_id | int(10) | | | 0 |
| rec_date | datetime | | | 2003-12-08 00:00:00 |
+---------------+---------------+------+-----+---------------------+
SELECT count(*) from go._iso;
+----------+
| count(*) |
+----------+
| 11498 |
+----------+
1 row in set (0.01 sec)
This was on the end of both describe results.
----------------+
Extra |
----------------+
auto_increment |
|
|
|
|
|
|
----------------+
--
Work:- postmasterAThinwick.demon.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
