Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL query logic problem

Status
Not open for further replies.
shifter480

shifter480

Technical User
Apr 28, 2009
12
GB
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
 
Sort by date Sort by votes
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 [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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

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

Joe
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BobThornton
  • Locked
  • Question
windows command in a sql script
Replies
1
Views
414
carp
carp
Tetol
  • Locked
  • Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
380
carp
carp
manbob
  • Locked
  • Question
need a help with sub query getting group ereor, please correct
Replies
2
Views
415
carp
carp
jchewsmith
  • Locked
  • Question
SQL Syntax 1
Replies
1
Views
364
JarlH
JarlH
jchewsmith
  • Locked
  • Question
Update Query with a MAX(Date) subquery 1
Replies
1
Views
452
JarlH
JarlH

Part and Inventory Search

Sponsor

Back
Top