List:General Discussion« Previous MessageNext Message »
From:hsv Date:June 13 2013 1:15pm
Subject:Re: How do I select all rows of table that have some rows in
another table (AND, not OR)
View as plain text  
>>>> 2013/06/11 12:59 -0700, Daevid Vincent >>>>
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;

scene_id  genres                  
--------  ------------------------
      17  1|3|10|19|38|53|58|59|  

SELECT * FROM scene_all_genres WHERE scene_id = 11;

scene_id  genres             
--------  -------------------
      11  1|10|19|31|32|59|  
<<<<<<<<
Except that, it seems to me, it somehow reflects the reality of assigning attributes to
the scenes (movies?) that you catalog. In a way, it looks very much like using a
bitstring wherin each place stands for one attribute. If, say, the bitstring for that
which the user gladly picks something is called "glad", and that for which the user is
loath to pick something is called "loath", an expression for fulfilling all attributes is
(glad & genre) = glad AND (loath & genre) = 0,
with no bit-telling. 

Thread
How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent11 Jun
  • Re: How do I select all rows of table that have some rows in anothertable (AND, not OR)shawn green11 Jun
    • RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent11 Jun
      • RE: How do I select all rows of table that have some rows inanother table (AND, not OR)Rick James12 Jun
        • RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent12 Jun
          • RE: How do I select all rows of table that have some rows inanother table (AND, not OR)Rick James12 Jun
      • Re: How do I select all rows of table that have some rows in anothertable (AND, not OR)shawn green12 Jun
  • Re: How do I select all rows of table that have some rows inanother table (AND, not OR)hsv13 Jun
RE: How do I select all rows of table that have some rows in another table (AND, not OR)Daevid Vincent12 Jun
Re: How do I select all rows of table that have some rows inanother table (AND, not OR)hsv13 Jun
RE: How do I select all rows of table that have some rows in another table (AND, not OR)hsv18 Jun