Databases
StackOverflow2013
Postsdbo
POmysql one-to-many query with negation and/or multiple criteria
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmysql one-to-many query with negation and/or multiple criteria
    primarykey
    data
    text
    <p>I thought a query like this would be pretty easy because of the nature of relational databases but it seems to be giving me a fit. I also searched around but found nothing that really helped. Here's the situation:</p> <p>Let's say I have a simple relationship for products and product tags. This is a one-to-many relationship, so we could have the following:</p> <pre><code>productid | tag ======================== 1 | Car 1 | Black 1 | Ford 2 | Car 2 | Red 2 | Ford 3 | Car 3 | Black 3 | Lexus 4 | Motorcycle 4 | Black 5 | Skateboard 5 | Black 6 | Skateboard 6 | Green </code></pre> <p>What's the <strong>most efficient</strong> way to query for all <code>(Ford OR Black OR Skateboard) AND NOT (Motorcycles OR Green)</code>? Another query I'm going to need to do is something like all <code>(Car) or (Skateboard) or (Green AND Motorcycle) or (Red AND Motorcycle)</code>. </p> <p>There are about 150k records in the products table and 600k records in the tags tables, so the query is going to need to be as efficient as possible. Here's one query that I've been messing around with (example #1), but it seems to be taking about 4 seconds or so. Any help would be much appreciated.</p> <pre><code>SELECT p.productid FROM products p JOIN producttags tag1 USING (productid) WHERE p.active = 1 AND tag1.tag IN ( 'Ford', 'Black', 'Skatebaord' ) AND p.productid NOT IN (SELECT productid FROM producttags WHERE tag IN ( 'Motorcycle', 'Green' )); </code></pre> <p>&nbsp; </p> <h1>Update</h1> <p>The quickest query I've found so far is something like this. It's taking 100-200ms but it seems pretty inflexible and ugly. Basically I'm grabbing all products that match <code>Ford</code>, <code>Black</code>, or <code>Skateboard</code>. Them I'm concatenating all of the tags for those matched products into a colon-separated string and removing all products that match on <code>:Green:</code> AND <code>:Motorcycle:</code>. Any thoughts?</p> <pre><code>SELECT p.productid, Concat(':', Group_concat(alltags.tag SEPARATOR ':'), ':') AS taglist FROM products p JOIN producttags tag1 USING (productid) JOIN producttags alltags USING (productid) WHERE p.active = 1 AND tag1.tag IN ( 'Ford', 'Black', 'Skateboard' ) GROUP BY tag1.productid HAVING ( taglist NOT LIKE '%:Motorcycle:%' AND taglist NOT LIKE '%:Green:%' ); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeff
    1. USJeff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmysql one-to-many query with negation and/or multiple criteria
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POmysql one-to-many query with negation and/or multiple criteria
      1. USJeff
      1. VTBountyStart
    3. VO
      singulars
      1. POmysql one-to-many query with negation and/or multiple criteria
      1. USSaidbakR
      1. VTFavorite
    1. COAre you able to execute the subquery for the productids for Motorcycle and Green in advance? If so, passing those into the in clause (or using not equals) will probably give you a better result.
      singulars
      1. POmysql one-to-many query with negation and/or multiple criteria
      1. USclieu
    2. COBecause of the structure I'm given it would be pretty difficult to accomplish, but it's definitely something I'll be toying around with.
      singulars
      1. POmysql one-to-many query with negation and/or multiple criteria
      1. USJeff
    3. COWhat indexes exist on the products and the tag tables? Also, what is the typical amount of products assigned to each tag? Are we talking 10, 100, 1000, 10000 products in each tag on average?
      singulars
      1. POmysql one-to-many query with negation and/or multiple criteria
      1. USD-Rock
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload