MySQL , How to give output of UNION to WHERE clause

Question

I have following SQL

SELECT tsd.ID
FROM test_series_details tsd
WHERE tsd.DIV_ID =1 
  AND tsd.END_DATE > NOW()
  AND tsd.ID NOT IN 
        ((SELECT tsr.TEST_ID
        FROM  test_series_results tsr
        WHERE tsr.STUDENT_ID=3)
    UNION 
        (SELECT tsrlv.TEST_ID
        FROM test_series_restore_log_viewer tsrlv
        WHERE tsrlv.STUDENT_ID=3
        GROUP BY tsrlv.TEST_ID))

I have tested below part of query and it is working,

(SELECT tsr.TEST_ID
        FROM  test_series_results tsr
        WHERE tsr.STUDENT_ID=3)
    UNION 
        (SELECT tsrlv.TEST_ID
        FROM test_series_restore_log_viewer tsrlv
        WHERE tsrlv.STUDENT_ID=3
        GROUP BY tsrlv.TEST_ID)

But how to give output of this UNION to IN condition?

ERROR IS AS follows

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT tsrlv.TEST_ID FROM test_series_restore_log_viewer tsrlv ' at line 9

Gordon Linoff · Accepted Answer · 2015-05-20 10:44:49Z

The best way to write this is as two separate clauses:

SELECT tsd.ID
FROM test_series_details tsd
WHERE tsd.DIV_ID = 1 AND
      tsd.END_DATE > NOW() AND
      tsd.ID NOT IN (SELECT tsr.TEST_ID
                     FROM test_series_results tsr
                     WHERE tsr.STUDENT_ID = 3
                    ) AND
      tsd.ID NOT IN (SELECT tsrlv.TEST_ID
                     FROM test_series_restore_log_viewer tsrlv
                     WHERE tsrlv.STUDENT_ID = 3
                    );

This allows the engine to take advantage of indexes on the two tables. In addition, the GROUP BY is not necessary (either when written this way or in your original formulation).

For performance -- and just in case the ids in the subqueries could ever be NULL -- I would be inclined to write this using NOT EXISTS:

SELECT tsd.ID
FROM test_series_details tsd
WHERE tsd.DIV_ID = 1 AND
      tsd.END_DATE > NOW() AND
      NOT EXISTS (SELECT 1
                  FROM test_series_results tsr
                  WHERE tsr.STUDENT_ID = 3 and tsr.TEST_ID = tsd.ID
                 ) AND
      NOT EXISTS (SELECT 1
                  FROM test_series_restore_log_viewer tsrlv
                  WHERE tsrlv.STUDENT_ID = 3 and tsrlv.TEST_ID = tsd.ID
                 );

The best indexes for this query are: test_series_results(test_id, student_id) and test_series_restore_log_viewer(test_id, student_id) and test_series_details(DIV_ID, end_date, id).

Robert · Accepted Answer · 2015-05-20 10:43:15Z

1

Try to use LEFT JOIN as below

SELECT tsd.ID
FROM test_series_details tsd
LEFT JOIN ((SELECT tsr.TEST_ID
        FROM  test_series_results tsr
        WHERE tsr.STUDENT_ID=3)
        UNION 
        (SELECT tsrlv.TEST_ID
        FROM test_series_restore_log_viewer tsrlv
        WHERE tsrlv.STUDENT_ID=3
        GROUP BY tsrlv.TEST_ID)) AS T on T.TEST_ID = tsd.ID 
WHERE tsd.DIV_ID =1 
  AND tsd.END_DATE > NOW()
  AND T.TEST_ID is null

answered May 20, 2015 at 10:43

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Comments

Abhik Chakraborty · Accepted Answer · 2015-05-20 10:44:06Z

1

How about now -

SELECT tsd.ID
FROM test_series_details tsd
WHERE 
tsd.DIV_ID =1 
AND tsd.END_DATE > NOW()
AND tsd.ID NOT IN (
   select TEST_ID from (
     (
        SELECT tsr.TEST_ID
        FROM  test_series_results tsr
        WHERE tsr.STUDENT_ID=3
     )
     UNION 
     (
        SELECT tsrlv.TEST_ID
        FROM test_series_restore_log_viewer tsrlv
        WHERE tsrlv.STUDENT_ID=3
        GROUP BY tsrlv.TEST_ID
     )
    )x
)

answered May 20, 2015 at 10:44

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Comments

Danyal Sandeelo · Accepted Answer · 2015-05-20 11:05:28Z

0

Try this

  SELECT tsd.ID
  FROM test_series_details tsd
  WHERE tsd.DIV_ID =1 
  AND tsd.END_DATE > NOW()
  AND tsd.ID NOT IN 
  (
      select  TEST_ID from (
       SELECT tsr.TEST_ID
       FROM  test_series_results tsr
       WHERE tsr.STUDENT_ID=3

       UNION 

       SELECT tsrlv.TEST_ID
       FROM test_series_restore_log_viewer tsrlv
       WHERE tsrlv.STUDENT_ID=3
       GROUP BY tsrlv.TEST_ID   
     )as alias 
   )

edited May 20, 2015 at 11:05

answered May 20, 2015 at 10:43

Danyal Sandeelo

12.4k10 gold badges57 silver badges90 bronze badges

Collectives™ on Stack Overflow

MySQL , How to give output of UNION to WHERE clause

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related