CODEWITHSUNDEEP
mysqlselectsql-like
Yury Lankovskiy
Yury Lankovskiy

Reputation: 153

NOT LIKE MySQL statement

I have an issue with NOT LIKE statement. I have two sql tables setup as tag map. First table finds tag_id according to the search name and second finds resource_id based on tag_id found. When I run a NOT LIKE statement below, I receive the result: resource_id = 1.

Tag map tables

tag_id name
1      meat
2      vegetarian

resource_id tag_id
1           1
1           2

Query

SELECT 
    f.id, f.food_name, tm.resource_id, tm.tag_id, t.name 
FROM 
    tag as t, tagmap as tm JOIN item as f ON 
    (
        f.id = tm.resource_id AND tm.tag_id IN 
        (
        SELECT 
            t.tag_id 
        FROM 
            tag as t 
        WHERE 
            t.name NOT LIKE '%meat%' 
        ) 
     )
GROUP by f.id

All I need this query to do is, if it finds a resource_id with tag name "meat" I don't want it to return this resource_id.

If my explanation is not clear please let me know.

Upvotes: 0

Views: 228

Answers (2)

Olaf Dietsche
Olaf Dietsche

Reputation: 74018

Then you must search for:

select resource_id
from tagmap
where resource_id not in (select resource_id
                          from tagmap
                          where tag_id in (select tag_id from tag
                                           where name like '%meat%'));

or with a join:

select *
from tagmap
where resource_id not in (select m.resource_id
                          from tagmap m, tag t
                          where m.tag_id = t.tag_id and t.name like '%meat%');

You look for resource_id with name "meat" and exclude these not in in your select.

This might correspond to your query, but I'm not really sure:

select f.id, f.food_name, tm.resource_id, tm.tag_id, t.name 
from tag as t, tagmap as tm, item as f
where f.id = tm.resource_id
      and tm.tag_id = t.tag_id
      and tm.resource_id not in (select m.resource_id
                                 from tagmap m, tag t
                                 where m.tag_id = t.tag_id
                                       and t.name like '%meat%')
group by f.id;

Upvotes: 1

Nesim Razon
Nesim Razon

Reputation: 9794

I am not %100 sure I understand what you expect on your query result, but from what I understand this might help you:

SELECT f.id, f.food_name, tm.resource_id, tm.tag_id, t.name 
FROM tag as t
left join tagmap as tm on tm.resource_id = t.tag_id
left join item as f on f.id = tm.resource_id
where t.name NOT LIKE '%meat%' 
GROUP by f.id

And to play with query, here sqlfiddle sample:

http://sqlfiddle.com/#!2/561c4/7

Edit:

So you want to exclude any resource attached to selected tag, try this:

SELECT f.id, f.food_name, tm.resource_id, tm.tag_id, t.name 
FROM tag AS t 
INNER JOIN tagmap AS tm ON tm.resource_id = t.tag_id 
INNER JOIN item AS f ON f.id = tm.resource_id 
where tm.resource_id not in (
  select resource_id from tagmap 
  where tag_id in (select tag_id from tag WHERE name LIKE '%meat%')
)  
GROUP by f.id

http://sqlfiddle.com/#!2/053fa/3

Upvotes: 1

Related Questions