SQL query logic problem

[shifter480]

Hi,

I have a query which I dont know how to solve..

The problem is that I am adding some data to this dynamically. These are the facility fields at the bottom "AND library = 'yes' OR garden = 'yes' OR language_lab = 'yes' OR self_study_center = 'yes'".

The trouble is that the facilities seem to make the other criteria in the where redundant.

For example, if I select no facilities and have the other default values there will be no results. An example below:

SELECT course.course_id, course.name AS course_name, course.level, course.price, course.info, course_type.type, school.school_id AS school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.accreditations FROM school_course JOIN course ON school_course.course_id = course.course_id RIGHT JOIN school ON school_course.school_id = school.school_id JOIN course_coursetype ON course.course_id = course_coursetype.course_id JOIN course_type ON course_coursetype.course_typeid = course_type.course_typeid WHERE region = 'scotland' AND course_type.course_typeid ='1' AND town='ascot' AND country='UK' ORDER BY school_name ASC

But if I have some facilities and it finds a match then it will ignore the other where region =.. town = ... etc and find a match purely on the OR for the facilities.
I need the facilities query to be an OR though as I want to be able to find a match of one or all of the facilities in the query. An example is below which will return results(because it has facilities).

SELECT course.course_id, course.name AS course_name, course.level, course.price, course.info, course_type.type, school.school_id AS school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.accreditations FROM school_course JOIN course ON school_course.course_id = course.course_id RIGHT JOIN school ON school_course.school_id = school.school_id JOIN course_coursetype ON course.course_id = course_coursetype.course_id JOIN course_type ON course_coursetype.course_typeid = course_type.course_typeid WHERE region = 'scotland' AND course_type.course_typeid ='1' AND town='ascot' AND country='UK' AND library = 'yes' OR garden = 'yes' OR language_lab = 'yes' OR self_study_center = 'yes' ORDER BY school_name ASC

Does anybody have any advice as I am at a loose end with this one!!

Thanks in advance!

Joe

 

[r937]

remember this from basic math...

1 + 2 + 3 * 4

the answer is 15, right?

that's because it is evaluated as 1 + 2 + ( 3 * 4 )

it is ~not~ evaluated as (1 + 2 + 3 ) * 4 = 24

multiplication takes precedence over addition

in the same way, ANDs take precedence over ORs

so your query says this --

WHERE region = 'scotland' 
  AND course_type.course_typeid ='1' 
  AND town='ascot' 
  AND country='UK' 
  AND library = 'yes' 
   OR garden = 'yes' 
   OR language_lab = 'yes' 
   OR self_study_center = 'yes'

and therefore it is evaluated like this --

WHERE [red](
      region = 'scotland' 
  AND course_type.course_typeid ='1' 
  AND town='ascot' 
  AND country='UK' 
  AND library = 'yes' 
      )[/red]
   OR [blue]garden = 'yes'[/blue]
   OR [blue]language_lab = 'yes'[/blue] 
   OR [blue]self_study_center = 'yes'[/blue]

can you see what you would need to do to make the query do what you want?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

As Rudy stated, use proper parenthesis.
Another way:
...
AND 'yes' IN (library,garden,language_lab,self_study_center)
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shifter480]

Yes what you are saying makes sense, sort of..

So with this in mind, would this query achieve the following.

WHERE region = 'scotland' AND course_type.course_typeid ='1' AND town='ascot' AND country='UK' AND we have a yes in one or many of these fields (library,garden,language_lab,self_study_center)??

So it would have to match all of the above ANDS plus one or many of the facilities?


SELECT course.course_id, course.name AS course_name, course.level, course.price, course.info, course_type.type, school.school_id AS school_id, school.name AS school_name, school.street, school.town, school.city, school.county, school.region, school.postcode, school.country, school.school_facts, school.general_info, school.accreditations FROM school_course JOIN course ON school_course.course_id = course.course_id RIGHT JOIN school ON school_course.school_id = school.school_id JOIN course_coursetype ON course.course_id = course_coursetype.course_id JOIN course_type ON course_coursetype.course_typeid = course_type.course_typeid WHERE region = 'scotland' AND course_type.course_typeid ='1' AND town='ascot' AND country='UK' AND 'yes' IN (library,garden,language_lab,self_study_center) ORDER BY school_name ASC

Thanks

 

[r937]

So it would have to match all of the above ANDS plus one or many of the facilities?

what happened when you tested it?




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[shifter480]

Well - Good question.. No results came back (as they would with no facilities selected) so thats a good start. However admittedly I need to add some more test data. I just wanted to know if I have the logic correct before I start changing my php code.

 

[r937]

I just wanted to know if I have the logic correct before I start changing my php code.

~always~ test your SQL outside of php first




r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[shifter480]

Ok thank you for your help I shall go off and test some more. I may be back though!!

Joe