×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL query logic problem

thread220-1611831

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

RE: SQL query logic problem

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 --

CODE

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 --

CODE

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'
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

RE: SQL query logic problem

(OP)
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
 

RE: SQL query logic problem

Quote:

So it would have to match all of the above ANDS plus one or many of the facilities?
what happened when you tested it?

smile

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

RE: SQL query logic problem

(OP)
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.

smile

RE: SQL query logic problem

Quote:

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

smile

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

RE: SQL query logic problem

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

Joe

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close