Search code examples
mysqlsubquery

How can set a select query to a variable and use it in another select query in mysql

I have 10 plus select queries. In all the queries I need to use a common sub query. So for reusability, I thought of putting sub query in a variable and use it in other select queries. Below is the sample code I'm trying but it is not working.

declare querySelect varchar(1000);
set querySelect="SELECT Id from orgs where orgType =5";

select * from organisation where Id in (select querySelect);
Solution

  • One option here would be to create a view:

    CREATE VIEW orgView AS
SELECT Id FROM orgs WHERE orgType = 5;

    Then select from this view in your subsequent queries, e.g.

    SELECT * FROM organisation WHERE Id IN (SELECT Id FROM orgView);