1

I have read that a select query that stores a result into variables does not return a result set. Is there an efficient way to do both?

BEGIN
   #return this and also use it in the following query
   select id, name, table2_id 
   from table1 
   where id = variable_id;

   select id, table2_name 
   from table2 
   where table2.id = table2.id from the previous result set
END 
0

Here you can use user defined variables, as long as you only have only one table2.id

BEGIN
   #return this and also use it in the following query
   select id, name, @id :=  table2_id 
   from table1 
   where id = variable_id;

   select id, table2_name 
   from table2 
   where table2.id = @id
END 

Or if you have more than one table2.id

BEGIN
   #return this and also use it in the following query
   select id, name, table2.id 
   from table1 
   where id = variable_id;

   select id, table2_name 
   from table2 
   where table2.id in (select table2<_id 
                       from table1 
                       where id = variable_id);
END 

Or you can join directly, and ave only one result set

BEGIN
   #return this and also use it in the following query
   select t1.id, t1.name, t1.table2_id,t2,id, t2.table2_name
   from table1 t1 INNER JOIN table2 t2 ON t2.id = t1.table2_id
   where t1.id = variable_id;
END
2
  • To clarify, I actually meant table2_id, not table2.id incase it makes a difference. I was trying to highlight that i had a foreign key in my table that i needed to join on, and then return that result and also use it in the following query. I think your first solution would work though.
    – cubesnyc
    Jul 24 '20 at 15:50
  • no it makes no difference at all, try the ssolutions, i added also a joined table where you only have4 one Resultset
    – nbk
    Jul 24 '20 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.