mysql duplicate subquery in where clause

Question

I need to do selection from mysql 5.7.22 in one query.

select id from t1 where type_id=(select type_id from t2 where id=1 limit 1) and id not in  
            (select obj_id from t2
                where 
                    type_id = (select type_id from t2 where id=1 limit 1)
                    and
                    type2_id = (select type2_id from t2 where id=1 limit  
                    ...
             )

I have some duplicate subquerys in where clause (it's only part of the query, this subquery duplicates many times)

'(select type_id from t2 where id=1 limit 1)'

Can I some how figure it out in one place, to reduce verbose. So I want to select once

select type_id, type2_id from t2 where id=1 limit 1

and make type_id, type2_id available in all query context.

I know mysql 8.0 has WITH syntax, but I am using 5.7.22

I want to do this in one query without transactions.

O. Jones · Accepted Answer · 2018-06-07 10:52:50Z

It's hard to give you complete advice without seeing your more of your query. But you have some choices.

You could try creating a view as follows then using it.

CREATE VIEW selector
    AS SELECT MAX(type_id) type_id, MAX(obj_id) obj_id
         FROM t2
        WHERE id = 1

It looks possible that the t2 query returns multiple rows. This view deals with that by using MAX() instead of LIMIT 1. But if t2.id is a primary key, then all you need is

CREATE VIEW selector
    AS SELECT type_id, obj_id
         FROM t2
        WHERE id = 1

Then you can use the view in your query.

For example

SELECT id
  FROM t1
 WHERE type_id = (SELECT type_id FROM selector)
   AND obj_id <> (SELECT obj_id FROM selector)

Or you could figure out how to use join operations rather than subqueries.

SELECT id
  FROM t1
  JOIN selector ON t1.type_id = selector.type_id AND t1.obj_id <> selector.obj_id

O. Jones · Accepted Answer · 2018-06-07 11:03:07Z

0

try this

select id from t1 ,(select type_id from t2 where id=1 limit 1)  t where type_id=t.type_id and id not in  
            (select obj_id from t2
                where 
                    type_id = t.type_id 
                    and
                    type2_id = t.type_id   
                    ...
             )

edited Jun 7, 2018 at 11:03

O. Jones

109k17 gold badges134 silver badges187 bronze badges

answered Jun 7, 2018 at 11:00

user3431530

11 bronze badge

Collectives™ on Stack Overflow

mysql duplicate subquery in where clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related