MySQL SELECT Rows Having Two Specific Values

MySQLselect

I have a single table containing nothing but productID and colorID. There are 10,000 rows in the table.

I need to be able to select all productID having colorID 2 and colorID 5. For example:

productID colorID
1         1
2         9
3         2
4         2
3         6
3         5
1         5

Using this data set I am looking for productID 3, since it is the only productID that has colorID 2 AND colorID 5 (it also has colorID 6, but I don't care about that)

Using WHERE colorID=2 AND colorID=6 returns 0 rows.

Using WHERE colorID IN (2,6) returns values for products that do not have both colorID 2 and 6.

This seems like a simple thing to query, but I am finding myself up against a wall. What's the best way to accomplish this in a reasonably efficient way?

Best Answer

If what you want is to find ProductID that has both 2 and 6 values, try below query:

-- Assuming table is named #tmp

SELECT
    a.*
FROM #tmp a 
INNER JOIN (
    SELECT
        ProductID,
        COUNT(DISTINCT ColorID) AS cnt
    FROM #tmp
    WHERE ColorID IN (2, 6)
    GROUP BY ProductID
    HAVING COUNT(DISTINCT ColorID) = 2) AS b ON a.ProductID = b.ProductID
WHERE a.ColorID IN (2, 6)

Related Solutions

PostgreSQL – Selecting Rows with Repeated Data in a Timeframe

SELECT *
FROM   task t1
WHERE  EXISTS (
   SELECT 1
   FROM   task t2
   WHERE  t2.reference = t1.reference  -- same reference
   AND    t2.task_id  <> t1.task_id    -- different task_id (exclude self-join)
   AND    t2.end_date BETWEEN t1.end_date - 30
                      AND     t1.end_date + 30 
   )

An EXISTS semi-join should be the fastest way to arrive at DISTINCT rows, because you don't get duplicates to begin with. Also, in the event of multiple matching rows (many matching tasks withing 30 days) evaluation can stop after the first match is found. I would expect this query to beat anything you have so far. Test with EXPLAIN ANALYZE.

In a WHERE clause or JOIN condition don't use an expression like:

abs(t1.end_date - t2.end_date) < 30

If you can avoid it. As the left hand expression is derived from values of two tables, the only way for the query planner is to form a limited CROSS JOIN (after applying other conditions) and compute a value for every possible combination. This is a well known anti-pattern for good performance!

As long as there are only few rows per reference, it won't matter much. But the cost grows with O(N²) with more rows per reference. I rewrote the condition to:

t2.end_date BETWEEN t1.end_date - 30 AND t1.end_date + 30

In my tests, the first form went from 5x slower to 500x slower quickly when I narrowed down the id-space for reference (-> more matching rows).

The second form can also more easily use indexes. If you want to select a small sample of the table, with conditions like:

reference > 450
date_end > now()::date

And an index like:

CREATE INDEX test_idx ON task (reference, date_end);
CREATE INDEX test_idx2 ON task (date_end);

The performance gap becomes even more overwhelming.

MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

My first thought would be to use the INFORMATION_SCHEMA first, so you get to know (in one query for all tables in the MySQL instance) which tables have an active column and then use that info to construct your queries. And this is probably the most sane approach.

There is one other, tricky way though that works no matter if the table has or not such a column:

SELECT 
  ( SELECT COUNT(*)
    FROM TableName AS t
    WHERE active = 1
  ) AS cnt
FROM
  ( SELECT 1 AS active
  ) AS dummy ;

Tested at SQL-Fiddle How it works?

If the table has a column named active, the query is "translated" as if it had:

    WHERE t.active = 1

If the table doesn't have a column named active, the query is "translated" as if it had:

    WHERE dummy.active = 1         -- which is true

Best Answer

Related Solutions

PostgreSQL – Selecting Rows with Repeated Data in a Timeframe

MySQL – How to Select Specific Rows if a Column Exists or All Rows if a Column Doesn’t

Related Question