Check out the MySQL manual for more help, or post yout existing query for me
to gawk at :)


on 24/09/02 11:24 PM, Naintara Jain ([EMAIL PROTECTED]) wrote:

> Thank you Chris.
> I agree with you wholeheartedly that the SQL should be optimized.
> Now, I don't want to freeload on your time, but I have a very complex query,
> which I have been pondering over for the last many days.
> If anyone can tell me what query will return just one row for the problem I
> have, s/he will be SQL Guru to me.
> My problem is:
> I have 3 tables (given below). A member of the staff takes courses and for
> each course he has requirement id, these details get stored in the
> staff_courses table. Each course taken will have a requirement specification
> which could be any of 4 values given below (after the reqs table). I need to
> get totals for each requirement for each member of the staff. I am using
> MySQL (and cannot use sub-queries). I have been able to get the requirement
> totals for each member using Group By reqid, but I get a separate row for
> each requirement against the member of staff. So if a member has taken 3
> courses, and one course has a reqid of 0 and the other two courses have a
> reqid of 1, I get 2 rows in the result set for that staffid, one will
> contain the reqid 0 and total as 1, while the second row will contain the
> reqid as 1 and the total as 2.
> Can I get the totals for each requirement in one row and can I do this in
> one query?
> 1)"staff" table
> staffid
> branchid
> 2)"staff_courses" table
> courseid
> staffid
> reqid
> 3)"reqs" table
> reqid
> requirement
> contains the
> reqid  requirement
> 0 No action
> 1 Primary requirement
> 2 Further requirement
> 3 Completed
> -----Original Message-----
> t]On Behalf Of Chris Hewitt
> Sent: Tuesday, September 24, 2002 1:49 PM
> Subject: Re: [PHP] in a logic loop!
> Naintara Jain wrote:
>> What I am doing is:
>> I maintain a set of details in say $prev_row (previous row) and another in
>> cur_row (current row).
>> The minute my cur_row detail (one unique id) doesn't match the prev_row
>> detail, I print out all the previous row details, and reinitialize the
>> various variables (all in a loop).
>> This works fine for all cases but fails for the last ID, because of the
>> logic used. Do I need to create a special case for the last one, or can
>> anyone suggest a better way?
> When you come out of your loop, either there will be one row left to
> print, or there were no matching rows. Suggestion one is to test for
> this and if there were matching rows, do a special case for the last row.
> Alternatively, you are printing a row when the ID changes, that is, the
> last row of the 1 to 3 matching rows. Why not print the first one, not
> last one? You change the logic
> to print the first row then ignore others with the same ID. It could be
> that (because of an Order By clause) the last row is the correct one and
> the data may differ between rows with the same ID.
> You say you do not want to do it in the SQL statement itself, but this
> really is the right place. If MySQL use a LIMIT clause to only get one
> row, if you cannot refine the SQL statement to only retrieve one row.
> Chris
> --
> PHP General Mailing List (
> To unsubscribe, visit:

PHP General Mailing List (
To unsubscribe, visit:

Reply via email to