Hi DN,
Boy SQL is damn powerful!!!
I see what you mean by having my head switch the way it is thinking and
a lot of penny's dropped this afternoon, Thankyou.
In the end I did this:
SELECT studentname_27.objectives_id,
studentname_27.data,
staff.name,
studentname_27.date,
_objectives.name,
_subjectHeadings.name,
_subjects.name
FROM studentname_27,
_objectives,
_subjectHeadings,
_subjects,
hinwick.staff
WHERE studentname_27.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'
AND staff.id = studentname_27.staff_id
ORDER BY _subjects.displayOrder,
_subjectHeadings.displayOrder,
_objectives.displayOrder;
Which nicely returned:-
#+---------------+------------+----------------+----------------+--------
-----------------------+----------+--------------------------------------
--------+
#| objectives_id | data | name | date | name |
name | name |
#+---------------+------------+----------------+----------------+--------
-----------------------+----------+--------------------------------------
--------+
#| 1895 | 1:3 4;2:8; | A member of staff | 20020123121855 | Can
Put on items of clothing: | DRESSING | Therapy and Care - Self Care and
Health Care |
#+---------------+------------+----------------+----------------+--------
-----------------------+----------+--------------------------------------
--------+
So there is no join is this sql statement, I say this cos I see no word
saying JOIN?
M.
In message <1b4001c1a751$0e7988c0$7215100a@jrbrown>, DL Neil
<[EMAIL PROTECTED]> writes
>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
>
>
--
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
