mySQL "not in" query throwing errors

Question

I'm basically attempting to use the NOT IN function twice during a mySQL query My NEW current query is as followed

SELECT `o`.`id`, `o`.`name`, `o`.`url`, `o`.`type`, `o`.`desc` FROM `offers` as `o` 
WHERE `o`.country_iso = '$country_iso' AND `o`.`id` not in 
(select distinct(offer_id) from conversions where ip = '$_SERVER[REMOTE_ADDR]' 
and converted != '0') AND `o`.`id` not in 
(select `offer_id` from `aff_disabled_offers` where `offer_id` = 'o.id' 
and `user_id` = '1') ORDER by rand() LIMIT $limit

The query works but for some reason it's completely ignoring this

AND `o`.`id` not in 
(select `offer_id` from `aff_disabled_offers` where `offer_id` = 'o.id')

Read about quoting: http://stackoverflow.com/questions/11321491/mysql-when-to-use-single-quotes-double-quotes-and-backticks/11321508#11321508 — Michael Berkowski, Jan 12 '13 at 00:44

Robin Manoli · Accepted Answer · 2013-01-12T00:48:13.303

1

You need to specify an operator between your conditions, such as AND or OR. You also need a valid query inside the NOT IN clause.

SELECT `o`.`id`, `o`.`name`, `o`.`url`, `o`.`type`, `o`.`desc` 
FROM `offers` as `o` 
WHERE `o`.country_iso = '$country_iso' 
AND `o`.`id` not in (select distinct(offer_id)
                     from converstions where
                     from conversions 
                     where ip = '$_SERVER[REMOTE_ADDR]' 
                     and converted != '0')
AND `o`.`somevar` not in (select `somevar` from 
                          `sometable` where `offer_id` = 'o.id')
ORDER by rand() LIMIT 0,6

edited Jan 12 '13 at 00:48

answered Jan 12 '13 at 00:41

Robin Manoli

2,162
2
25
30

That's not worked either for some reason, Here's the code i used ` $sql = "SELECT `o`.`id`, `o`.`name`, `o`.`url`, `o`.`type`, `o`.`desc` FROM `offers` as `o` WHERE `o`.country_iso = '$country_iso' AND `o`.`id` not in (select distinct(offer_id) AND `o`.`id` not in (select `offer_id` from `aff_disabled_offers` where `offer_id` = 'o.id' and `user_id` = '1') FROM conversions where ip = '$_SERVER[REMOTE_ADDR]' and converted != '0') order by rand()";` – Curtis Crewe Jan 12 '13 at 00:46
Some code in my post was misplaced. As you can see (select distinct(offer_id) is incomplete. I have updated my answer now. – Robin Manoli Jan 12 '13 at 00:48
I have updated my current post to explain the problem at the moment – Curtis Crewe Jan 12 '13 at 00:56

mySQL "not in" query throwing errors

1 Answers1