Daevid Vincent How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 11, 2013; 12:59
Daevid Vincent
How do I select all rows of table that have some rows in another table (AND, not OR)
I am trying to implement a filter so that a user could select various genres they want "in" or "out". Perhaps they like 'action' and 'car chases' but don't like 'foreign' and 'drifting' (or whatever...)
So I want something sort of like this, however IN() is using an "OR" comparison when I need it to be an "AND"
SELECT DISTINCT s.`scene_id` AS `id`, GROUP_CONCAT(sg.`genre_id`) FROM `dvds` AS d JOIN `scenes_list` AS s ON s.`dvd_id` = d.`dvd_id` JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN (10,38) AND sg.`genre_id` NOT IN (22,61) GROUP BY s.`scene_id`;
This is giving me way way too many rows returned.
For example, I would expect this scene_id to be in the result set:
scene_id genre_id -------- ---------- 11 1 11 10 <-- 11 19 11 31 11 32 <-- but does not have 38 11 59
I've tried various subselect ideas, but of course this fails b/c genre_id can't be multiple things at one time (AND)
JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND sg.`genre_id` IN ( SELECT `genre_id` FROM `scenes_genres` WHERE `genre_id` = 10 AND `genre_id` = 38 AND `genre_id` <> 22 AND `genre_id` <> 61 )
And straight up like this failure too...
JOIN `scenes_genres` AS sg ON sg.`scene_id` = s.`scene_id` AND (sg.`genre_id` = 10 AND sg.`genre_id` = 38) AND NOT (sg.`genre_id` = 22 AND sg.`genre_id` = 61)
So I'm sort of out of ideas at this point and hoping someone has a way to do this.
Also, just for S&G this is how we are currently implementing it, but we feel the REGEXP is killing our queries and while "clever" is a bit hacky and nullifies any indexes we have on the genres column as it requires a file_sort table scan to compare substrings basically...
SELECT * FROM scene_all_genres WHERE scene_id = 17;
SELECT DISTINCT s.`scene_id` AS `id`, sg.`genres` FROM `scene_all_genres` AS sg, `dvds` AS d, `scenes_list` AS s WHERE dvd_id` = d.`dvd_id` AND sg.`scene_id` = s.`scene_id` AND sg.`genres` REGEXP '[[:<:]]10[[:>:]].*[[:<:]]38[[:>:]]' AND sg.`genres` NOT REGEXP '(([[:<:]]22[[:>:]])|([[:<:]]61[[:>:]]))'
These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Jun 11
shawn green Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 11, 2013; 17:16
shawn green
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 11
Daevid Vincent RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 11, 2013; 16:17
Daevid Vincent
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12
Rick James RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12, 2013; 16:39
Rick James
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12
shawn green Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12, 2013; 13:57
shawn green
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12
Daevid Vincent RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12, 2013; 11:30
Daevid Vincent
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12
Daevid Vincent RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12, 2013; 11:25
Daevid Vincent
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12
Rick James RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12, 2013; 23:45
Rick James
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12
hsv Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 12, 2013; 22:57
hsv
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 13
hsv Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 13, 2013; 09:15
hsv
Re: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 17
hsv RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Jun 17, 2013; 17:28
hsv
RE: How do I select all rows of table that have some rows in another table (AND, not OR)
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!