Re: in a logic loop!

SELECT DISTINCT * FROM table WHERE ..... Check out the MySQL manual for more help, or post yout existing query for me to gawk at :) HTH Justin on 24/09/02 11:24 PM, Naintara Jain (naintara@del6.vsnl.net.in) 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----- > From: php-general-return-117383-naintara=del6.vsnl.net.in@lists.php.net > [mailto:php-general-return-117383-naintara=del6.vsnl.net.in@lists.php.ne > t]On Behalf Of Chris Hewitt > Sent: Tuesday, September 24, 2002 1:49 PM > To: naintara@del6.vsnl.net.in > Cc: php-general@lists.php.net > 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. > > HTH > Chris > > > > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > > >