Jump to content

Nesting operators


fife
 Share

Recommended Posts

I have a query that is very messy.  I have tried using () to seperate everything and when I do it just all goes wrong.

 

here is the query

 


SELECT posts.*, users.*, countries.countryID, countries.country FROM posts 
INNER JOIN users ON users.userID = posts.postUserID INNER JOIN countries ON countries.countryID = users.userCountry WHERE posts.cityID = '".$row_rs_city['cityID']."'  AND posts.type = 'sightseeing' OR posts.cityID = '".$row_rs_city['cityID']."' AND posts.type = 'Inner city sightseeing' OR posts.cityID = '".$row_rs_city['cityID']."' AND posts.type = 'Outer city sightseeing'  ORDER BY posts.postID DESC

 

Is there a way of re-writing this so that it has brackets seperating everything and it will still work.  It works for the minute but re- writing posts.cityID = '".$row_rs_city['cityID']."' inbetween every OR just seems wrong.

 

Thanks for your help guys

Link to comment
Share on other sites

Your WHERE condition doesn't make much sense:

 

SELECT posts.*,
       users.*,
       countries.countryID,
       countries.country
FROM posts 
INNER JOIN users ON (users.userID = posts.postUserID)
INNER JOIN countries ON (countries.countryID = users.userCountry)
WHERE posts.cityID = '".$row_rs_city['cityID']."'
  AND posts.type = 'sightseeing'
   OR posts.cityID = '".$row_rs_city['cityID']."'
  AND posts.type = 'Inner city sightseeing'
   OR posts.cityID = '".$row_rs_city['cityID']."'
  AND posts.type = 'Outer city sightseeing'
ORDER BY posts.postID DESC

 

What's the logic behind it, in English? That very much dictates where the parentheses should go.

Link to comment
Share on other sites

Ive solved it with a new operator I'd never used or seen before. Here is the fixed query

 

SELECT posts.*, users.*, countries.countryID, countries.country FROM posts 
INNER JOIN users ON users.userID = posts.postUserID 
INNER JOIN countries ON countries.countryID = users.userCountry 
WHERE posts.cityID = '".$row_rs_city['cityID']."'
AND 
posts.type IN ('sightseeing', 'Inner city sightseeing', 'Outer city sightseeing') 
ORDER BY posts.postID DESC

 

Thanks anyway guys

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.