what order do filters get applied

This topic is locked

what order do filters get applied	8/8/2008 5:18:30 PM
PHPRunner General questions
T thesofa author Hi, I have spent hours on this one, it is bound to be something simple that I am doing wrong, so please help an old guy cross this road as a good turn! I have to prepare a report of those pupils who have not got any "naughty points" . This was not too difficult, as I used something like this `SELECT nd_pupils.p_lastname FROM nd_pupils Left Join nd_behaviours ON nd_behaviours.pup_id = nd_pupils.id_pup WHERE nd_behaviours.id_det IS NULL` This showed me all the pupils who have not got an entry in the behaviours table, as evinced by an absence of a record with an entry in the id_det field. That works fine, but then, once again, my goal posts have been moved. The new query has to allow for searching for any pupils who have no "naughty points" between two variable dates, so I need a search optioin for the dates. BUT, teachers being teachers, they cannot work a calender, so I have to allow them to search for dates in the form of academic weeks. These are gleaned from another look up table in the form of Date week each week has 5 dates that apply to the week, As the naughty points are awarded, the date given is recorded. So my advanced search page has Week, between and two boxes for numbers between 1 and 40. Strangely enough, teachers only work 40 weeks of the year. And only from 8.55 a.m. to 3.00 p.m. So now the query looks like this SELECT p.id_pup, Concat(p.p_firstname, " ", p.`Middle name(s)`, " ", p.p_lastname) AS Pupil, p.reg AS `Tutor Group`, w.week, COUNT(w.week) AS Tracking FROM nd_pupils AS p LEFT OUTER JOIN nd_behaviours AS b ON p.id_pup = b.pup_id LEFT OUTER JOIN weeks AS w ON b.datestamp = w.`date` GROUP BY p.id_pup, p.reg HAVING COUNT(w.week) < '1' The left outer joins allow me to get all the records . this produces the same set of results as the query above, not a bad guess was it. BUT when I search for the weeks between, I get a blank screen Now I have used joins, no where clause, as well as a Having clause as I remember from years ago that the having was applied after the search was done, what is going wrong please? Thought about this a bit more, what I need to do is first of all filter the "naughty point" records by date, so I have only those kids awarded naughty points during the period specified, THEN i need to apply the left outer join to find the kids who do NOT have naughty points awarded, then I can group them and use the output for the report. How can I force PHPR to do this, do I need a 2 stage query somehow, create a temp table and apply the second half of the query to the temp table? All help gratefully accepted.

Mark as Solved Mark as not solved

4 replies


J	Jane 8/11/2008
	Hi, Please publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages along with instructions on reproducing this error. I'll find what's wrong with your project inspecting it at Demo account site.


T	thesofa author 8/11/2008
	Done


T	thesofa author 8/12/2008
	OK, I have tried a different approach here with this query `SELECT * from nd_pupils p LEFT JOIN (SELECT * FROM nd_behaviours b JOIN weeks w ON b.datestamp=w.date WHERE w.week >0 AND w.week<20) bh ON p.id_pup=bh.pup_id WHERE bh.id_det is NULL GROUP BY p.id_pup` By varying the values in the inner sub-query WHERE line, I can achieve correct results. BUT, if i try to search on the `week` value, I get an error Technical information Error type 256 Error description Unknown column 'nd_behaviours.week' in 'where clause' URL demo.asprunner.net/phprunner_thesofa_co_uk/beh_13/RepNoTracking_report.php? Error file C:\UserAccounts\www\phprunner_thesofa_co_uk\beh_13_20080812062031\include\dbconnection.php Error line 26 SQL query SELECT from nd_pupils p LEFT JOIN (SELECT FROM nd_behaviours b JOIN weeks w ON b.datestamp=w.date WHERE w.week >0 AND w.week<20) bh ON p.id_pup=bh.pup_id where (bh.id_det is NULL ) and (1=1 and `nd_behaviours`.`week`>=1 and `nd_behaviours`.`week`<=20) GROUP BY p.id_pup order by 7 and I now realise that the search by date needs to be done inside the subquery before the LEFT OUTER JOIN is done, so the number of kids With behaviours is determined before they outer join sorts the ones Without behaviours. So I need to interrupt the search process and change the search string after the Search button is pressed but before the search string is sent to the database. I need to remove the two references to "nd_behaviours`.`week" and replace the values in those two into the previous line "WHERE w.week >0 AND w.week<20", then it might work. How can I do this?


B	bozzo 11/15/2008
	OK, I have tried a different approach here with this query `SELECT * from nd_pupils p LEFT JOIN (SELECT * FROM nd_behaviours b JOIN weeks w ON b.datestamp=w.date WHERE w.week >0 AND w.week<20) bh ON p.id_pup=bh.pup_id WHERE bh.id_det is NULL GROUP BY p.id_pup` By varying the values in the inner sub-query WHERE line, I can achieve correct results. BUT, if i try to search on the `week` value, I get an error and I now realise that the search by date needs to be done inside the subquery before the LEFT OUTER JOIN is done, so the number of kids With behaviours is determined before they outer join sorts the ones Without behaviours. So I need to interrupt the search process and change the search string after the Search button is pressed but before the search string is sent to the database. I need to remove the two references to "nd_behaviours`.`week" and replace the values in those two into the previous line "WHERE w.week >0 AND w.week<20", then it might work. How can I do this? HAve the same issue but not with joins....with a group by subquery! If any clue, gimme a light!

4 replies

Mark as Solved Reply