CODEWITHSUNDEEP
mysqljoinfilter
Peter
Peter

Reputation: 23

Apply filter on joined tables with MySQL

I store some data in MySQL and want to filter out rows that match a given criteria.

Sounds easy, but it is not since there are some join criteria involved.

I do have the following tables:

items : id, ...
genres: id, name:varchar, item_id

Each item has multiple genres.

The query should filter out items if at least one genre does match a given genre name (or a set of names).

For example:

Item with id 1 has 3 genres
  - genre name = 'foo'
  - genre name = 'bar'
  - genre name = 'baz'

Item 1 may not be part of the result set if the given genre name is 'bar', ['bar', 'baz', 'xyz'], etc.

I tried to left join the genres on the items and applied a WHERE statement with "genres.name NOT IN (?)". ? is the given set of genre names.

This (of course) only works for items with exactly one genre. The same could be achieved by multiple WHERE conditions: WHERE name <> 'a' AND name <> 'b' ...

Any ideas how to get this query done properly?

Thanks in advance!

Upvotes: 1

Views: 7705

Answers (4)

Ravi Jain
Ravi Jain

Reputation: 1482

select * from items i where not exists 
(select '' from genres gen where gen.item_id = i.id and gen.name in ('foo','pop') )

A shorter ans simpler query. The inner query checks genre name in the keywords list , if the no row is returned then the current row of outer query is selected.
Verify the output at here.

Thanks to Kyra ,i borrowed his schema from sql fiddle.

Upvotes: 0

Gabe Ortiz
Gabe Ortiz

Reputation: 147

Well hopefully I understand what you are asking for and I think MySQL and T-SQL will be similar in this respect. These are the sample tables I assumed you were using from your example.

Table1
ID  NAME
1   Item 1
2   Item 2
3   Item 3
4   Item 4
5   Item 5
6   Item 6

Table2
ID  NAME    ITEM_ID
1   Genre 1   1
2   Genre 2   1
3   Genre 3   1
4   Genre 1   2
5   Genre 2   2
6   Genre 3   2
7   Genre 1   3
8   Genre 2   3
9   Genre 3   3
10  Genre 1   4

Here is the SQL to filter out items if they match one of your criteria.

SELECT *
FROM table1 a, table2 b
WHERE a.id = b.item_id
AND b.name NOT IN ('Genre 1', 'Genre 2')

Results returned from above query on sample tables.

ID  NAME    ID_1    NAME_1  ITEM_ID
1   Item 1    3 Genre 3   1
2   Item 2    6 Genre 3   2
3   Item 3    9 Genre 3   3

You could also filter out against the genre_id (table2.id) instead. If you plan to use a sub-query instead of hard coded values then you should switch the "NOT IN" to a "NOT EXISTS" and re-write the "AND" portion accordingly as "NOT IN" does not like null values.

This query also assumes that each "Item" has at least 1 "Genre." I am sure you can play with it if you want all "Items" excluding the ones that match your criteria.

Upvotes: 0

Kyra
Kyra

Reputation: 5407

You can do something like this (see sqlfiddle):

select i.name as item, g.name as genre
from items as i
left join genres as g
  on i.id = g.item_id 
where i.id not in
      (select distinct g2.item_id from genres as g2
       where FIND_IN_SET(g2.name,'foo,bar'));

And this way it works if you want to check against multiple genre names.

Upvotes: 1

mechanical_meat
mechanical_meat

Reputation: 169354

You can use a correlated subquery.
Example: 

SELECT id 
FROM   items i
WHERE  NOT EXISTS (SELECT 1 
                   FROM   genres g
                   WHERE  g.`name` = 'bar' 
                   AND    i.id = g.item_id);

A correlated subquery differs from other subqueries in that it references the outer query.
Correlated subqueries are run for each row projected/selected by the outer query.

Upvotes: 0

Related Questions