Hi Mark,
> Can anyone tell me if this can be done in a single SQL statement?
This sounds like solving the problem in procedural logic. Try putting that aside
and/or converting to relational
logic. As to the complexity of working with so many tables, its realisation is
startlingly simple once you
become familiar with the principles.
This is quite a large/involved topic. If you are not familiar with SQL and relational
techniques then instead of
attempting to read it in an email msg, it would be better to check out some of the
introductory articles on the
various PHP-support sites (start with the links at PHP.net); or getting hold of an
introductory text book.
Establish the linkage/relationship between each pair of tables using a 'join'.
Establish the sequence of the resultset by using the ORDER BY clause of a SELECT
statement.
SELECT *
FROM _worsdallm
_objectives
_subjectHeadings
_subjects
WHERE _worsdall.objectives_id = _objectives.id
AND _objectives.active = Y
AND _objectives.subjectHeadings_id = _subjectHeadings.id
AND _subjectHeadings.active = Y
AND _objectives.subjects_id = _subjects.id
AND _subjects.active = Y
ORDER BY _subjects.displayOrder,
_subjectHeadings.displayOrder,
_objectives.displayOrder
(without any testing, or any attempt at rationalisation or optimisation)
Regards,
=dn
> 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
---------------------------------------------------------------------
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
