Hi All,
Can anyone tell me if this can be done in a single SQL statement?
Well I have this data stored in a table called _worsdallm which looks
like this:-
id int(10) NOT NULL auto_increment
objectives_id int(10)
data varchar(254)
staff_id int(10)
date timestamp NOT NULL
PRIMARY KEY (id))
Now there will be lots of data in this table, objectives_id is the key
column here as that refers to an entry in table _objectives which looks
like this:-
id
subjects_id
subjectHeadings_id
name
active
displayOrder
So _worsdall.objectives_id refers to _objectives.id, now we are not
interested in this bit of data if _objectives.active != Y
_subjectHeadings table looks like:-
id
subjects_id
name
active
displayOrder
and so _objectives.subjectHeadings_id refers to _subjectHeadings.id, now
we are not interested in this subjectHeading if _subjectHeadings.active
!= Y
_subjects table looks like:-
id
name
active
displayOrder
and so _objectives.subjects_id refers to _subjects.id, now we are not
interested in this subjects if _subjects.active != Y
Assuming we are interested:-) here is the complex part (which I could do
in code but want to see if I can do it SQL).
I have to display the objectives recorded in the display order, i.e. by
the column displayOrder which is in all three tables.
Since the table _worsdallm has no idea of the order of things I would
have to go through in code........
for subjects (ORDER BY displayOrder) if active
for subjectHeadings (ORDER BY displayOrder) if active
for objectives (ORDER BY displayOrder) if active
does the objective exist in the table _worsdallm
As you can see I can do it in code but I would be hitting the SQL server
a lot more than necessary, since there are 2216 objectives and 450
Subject headings and 22 subjects and there may only be 22 records in
_worsdallm.
So how to construct an SQL statement to do it server friendly?
All the records in _worsdallm will be used unless they fail the active
tests for the objective they the record is AND they must be ordered by
the following way:-
subjects ORDER BY displayOrder
subjectHeadings ORDER BY displayOrder
objectives ORDER BY displayOrder
I am going round in circles here.....:-(
M.
--
Work:- postmasterAThinwick.demon.co.uk
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
