Question 20496 in Mysql Question posted in
The official MySQL documentation can be found here.

Is there a what to simplify this statement? – Mysql

It wouldn’t surprise me if there were already an answer to this type of problem, but I don’t know how to ask it.

The statement compares the task keys in the task table against the task/tag key combinations in another table.

I use PHP to execute this SQL, which compares values in one table against the values of another table. It works perfectly, but it looks ridiculously inefficient. It uses the same inner select statement three times to compare against a single value each time.

SELECT * FROM ttasks t
WHERE bComplete = 0
  AND ( 1 IN (SELECT iTagKey FROM ttagties WHERE t.taskKey = iLinkKey AND iType = 0)
        OR 3 IN (SELECT iTagKey FROM ttagties WHERE t.taskKey = iLinkKey AND iType = 0) )
  AND 10 NOT IN (SELECT iTagKey FROM ttagties WHERE t.taskKey = iLinkKey AND iType = 0)
ORDER BY dCreated;

Is there something like:

WHERE bComplete = 0
  AND ((1 OR 3) AND NOT 10) IN (SELECT iTagKey FROM ttagties WHERE t.taskKey = iLinkKey AND iType = 0)

or maybe

WHERE bComplete = 0
  AND ASSIGN SET AS (SELECT iTagKey FROM ttagties WHERE t.taskKey = iLinkKey AND iType = 0)
  AND (1 IN (SET) OR 3 IN (SET)) AND 10 NOT IN (SET)

Even if you can’t find a better way of writing the statement, could you suggest some better ways to describe the problem?

To clarify, the SELECT in the parentheses could be a set like (1,5,6,10). I haven’t found a JOIN that rejects the set because it has a 10, even though it also has a 1.

Here is the complete dataset for table ttagties:

CREATE TABLE ttagties (
  iLinkKey INT,
  iTagKey INT,
  iType INT);

INSERT INTO ttagties VALUES
(1,3,0),(2,1,0),(2,10,0),(3,10,0),(4,1,0),(4,10,0),(7,1,0),(7,10,0),(8,1,0),
(8,5,0),(8,10,0),(11,1,0),(11,10,0),(14,1,0),(14,10,0),(19,3,0),(19,10,0),
(21,3,0),(21,10,0),(23,3,0),(23,10,0),(25,3,0),(28,2,0),(29,2,0),(30,1,0),
(30,10,0),(31,2,0),(32,1,0),(32,6,0),(32,10,0),(33,1,0),(33,10,0),(34,2,0),
(34,6,0),(35,2,0),(36,3,0),(37,3,0),(38,1,0),(38,10,0),(39,2,0),(48,3,0),
(48,10,0),(51,1,0),(51,5,0),(51,7,0),(51,10,0),(54,1,0),(54,10,0),(55,2,0),
(57,10,0),(58,3,0),(58,10,0),(58,11,0),(60,1,0),(60,5,0),(60,10,0),(67,2,0),
(68,2,0),(69,10,0),(70,10,0),(71,2,0);
Tags:

3

Answers


  1. 0

    A combination of JOIN, GROUP_CONCAT(), HAVING and FIND_IN_SET() functions could be an alternative:

    WITH cte AS (
  SELECT t.taskKey, t.bComplete, t.dCreated, GROUP_CONCAT(iTagKey) AS iTagKeys
  FROM ttasks t 
  JOIN ttagties tt ON t.taskKey = tt.iLinkKey 
WHERE t.bComplete = 0
  AND tt.iType=0
GROUP BY t.taskKey, t.bComplete, t.dCreated
ORDER BY t.dCreated) 
  
SELECT taskKey, bComplete, dCreated
  FROM cte
WHERE (FIND_IN_SET(1,iTagKeys) OR FIND_IN_SET(3,iTagKeys))
  AND NOT FIND_IN_SET(10,iTagKeys);

    Or maybe like this:

    WITH cte AS (
  SELECT t.taskKey, t.bComplete, t.dCreated, 
       MIN(CASE WHEN iTagKey IN (1,3) THEN 1 ELSE 0 END) AS iTagKeys
  FROM ttasks t 
  JOIN ttagties tt ON t.taskKey = tt.iLinkKey 
WHERE t.bComplete = 0
  AND tt.iType=0
GROUP BY t.taskKey, t.bComplete, t.dCreated) 
  
SELECT taskKey, bComplete, dCreated
  FROM cte
WHERE iTagKeys=1

    Here’s a fiddle

    Login or Signup to reply.
  2. 0

    You could use EXISTS and NOT EXISTS to re-write your query

    SELECT * 
FROM ttasks t 
WHERE t.bComplete = 0
  AND EXISTS 
    (SELECT 1 
     FROM ttagties t1 
     WHERE t.taskKey = t1.iLinkKey AND t1.iType = 0 AND t1.iTagKey IN (1,3)) 
  AND NOT EXISTS 
    (SELECT 1 
     FROM ttagties t2 
     WHERE t.taskKey = t2.iLinkKey AND t2.iType = 0 AND t2.iTagKey = 10) 
ORDER BY t.dCreated;
    Login or Signup to reply.
  3. 0

    You must join to the tag table twice; once to find a hit on 1 or 3 and again to assert there is no hit on 10:

    select distinct t.* 
from ttasks t 
join ttagties tt on t.taskKey = tt.iLinkKey
  and tt.iTagKey IN (1,3)
  and tt.iType=0
left join ttagties tt2 on t.taskKey = tt2.iLinkKey
  and tt2.iTagKey = 10
  and tt2.iType=0
where t.bComplete = 0
and tt2.iLinkKey is null
order by t.dCreated

    Using distinct removes duplicates in case there are multiple hits on 1, 3.

    The check for null asserts that the join to 10 tagkeys failed to find any.

    This can be simplified somewhat, probably at the cost of performance, as follows:

    with tags as (
    select iLinkKey, iTagKey
    from ttagties where iTagKey IN (1,3,10)
    where iType = 0
)
select distinct t.*
from ttasks t 
join tags tt on t.taskKey = tt.iLinkKey
  and iTagKey != 10
left join tags tt2 on t.taskKey = tt2.iLinkKey
  and tt2.iTagKey = 10
where t.bComplete = 0
and tt2.iLinkKey is null
order by t.dCreated

    but the style gain, if any, is marginal.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search