CODEWITHSUNDEEP
mysql
Doktor83
Doktor83

Reputation: 27

Mysql query returns all rows. Why?

SELECT SQL_CALC_FOUND_ROWS o . * , titles.title AS title, categories.category AS category,

images.image AS image, urls.url AS url, descriptions.description AS description,

from_sites.from_site AS from_site, prices.price AS price

FROM oglasi AS o

LEFT JOIN titles ON o.title_id = titles.title_id

LEFT JOIN categories ON o.category_id = categories.category_id

LEFT JOIN images ON o.image_id = images.image_id

LEFT JOIN urls ON o.url_id = urls.url_id

LEFT JOIN descriptions ON o.description_id = descriptions.description_id

LEFT JOIN from_sites ON o.from_site_id = from_sites.from_site_id

LEFT JOIN prices ON o.price_id = prices.price_id

WHERE categories.category_id = "28"

OR categories.category_id = "29"

OR categories.category_id = "30"

OR categories.category_id = "31"

OR categories.category_id = "32"

OR categories.category_id = "33"

OR categories.category_id = "34"

OR categories.category_id = "35"

AND from_sites.from_site_id =7

ORDER BY o.izdvojen, titles.title ASC

The only problem here is that AND from_sites.from_site_id=7 is ignored. So it list's all of the subcats from all of sites. But when i try this same query only with one category_id and the from_site_id , then it works how it's supposed.

Can please someone tell me what's wrong here ?

Upvotes: 0

Views: 145

Answers (3)

awm
awm

Reputation: 6570

There's a lot of stuff you don't seem to need in the original query. May I suggest a simplified version?

SELECT title, category, image, url, description, from_site, price
FROM oglasi
LEFT JOIN titles USING (title_id)
LEFT JOIN categories USING (category_id)
LEFT JOIN images USING (image_id)
LEFT JOIN urls USING (url_id)
LEFT JOIN descriptions USING (description_id)
LEFT JOIN from_sites USING (from_site_id)
LEFT JOIN prices USING (price_id)
WHERE category_id IN (28,29,30,31,32,33,34,35) AND from_site_id =7
ORDER BY izdvojen, title

Upvotes: 0

Yaakov Ellis
Yaakov Ellis

Reputation: 41490

Try using parentheses to dictate how the logical operations are evaluated.

If you want something that is in any of the listed categories and in addition to that, is from_site_id = 7, then your where section should look like this:

WHERE (
      categories.category_id = "28"
      OR categories.category_id = "29"
      OR categories.category_id = "30"
      OR categories.category_id = "31"
      OR categories.category_id = "32"
      OR categories.category_id = "33"
      OR categories.category_id = "34"
      OR categories.category_id = "35"
      )
      AND from_sites.from_site_id =7

Your current query is saying to return all rows where category_id = 28-34 OR is from category 35 and is fromSiteID = 7 (so the fromSiteID condition is ignored for all but categoryID 35).

Upvotes: 1

user479911
user479911

Reputation:

You need to use parenthesis to explicitly tell MySQL which clauses to calculate first.

Try this:

WHERE (categories.category_id = "28"
OR categories.category_id = "29"
OR categories.category_id = "30"
OR categories.category_id = "31"
OR categories.category_id = "32"
OR categories.category_id = "33"
OR categories.category_id = "34"
OR categories.category_id = "35")
AND from_sites.from_site_id =7

Or something similar if that wasn't exactly what you meant.

Upvotes: 4

Related Questions