SQL query logic problem
SQL query logic problem
(OP)
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
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
RE: SQL query logic problem
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 --
CODE
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'
CODE
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'
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL query logic problem
Another way:
...
AND 'yes' IN (library,garden,language_lab,self_study_center)
...
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL query logic problem
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
RE: SQL query logic problem
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL query logic problem
RE: SQL query logic problem
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: SQL query logic problem
Joe