'mysql not in statement, not working

Hi I have the following query, however it still gives me results that are specified not to give me. what is wrong with this not in ?

select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%180211%' and taxonomic_units.tsn not in ('180212') order by rand() limit 1

select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%180210%' and taxonomic_units.tsn not in ('180212,573165') order by rand() limit 1

select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%180130%' and taxonomic_units.tsn not in ('180212,573165,573165') order by rand() limit 1

select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%179913%' and taxonomic_units.tsn not in ('180212,573165,573165,585192') order by rand() limit 1

select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id from hierarchy left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn where taxonomic_units.rank_id = 220 and hierarchy.hierarchy_string LIKE '%158852%' and taxonomic_units.tsn not in ('180212,573165,573165,585192,624896') order by rand() limit 1

here is the results with the duplicates, my not in statement was suppose to filter those out but it doesnt.

0   180212
1   573165
2   573165
3   632899
4   632141
5   647171
mysql


Solution 1:[1]

Use this query:

select taxonomic_units.tsn, hierarchy.hierarchy_string, hierarchy.TSN, taxonomic_units.rank_id   
   from hierarchy 
       left join taxonomic_units on hierarchy.TSN = taxonomic_units.tsn 
       where taxonomic_units.rank_id = 220 
             and hierarchy.hierarchy_string LIKE '%158852%' 
             and taxonomic_units.tsn not in ('180212','573165','573165','585192','624896') 
      order by rand() limit 1

You need to add the ' on all the values as these are different values. And you have given at the starting and at the end it treated as a single string.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Code Lღver

Related Questions