using field of subquery in output

Question

Query:

SELECT tbl1field  
FROM tbl1 
WHERE  id = (SELECT t2id 
             FROM tbl2 
             WHERE anotherid = (SELECT t3id FROM tbl3 )
             )

here tbl3 is having 2 fields : t3id and t3key

the output shows the corresponding value as follows

tbl1field
somedata

what I want is to map the t3key also to the actual output of the query :

expected output:

tbl1field	t3key
somedata	t3keyval

MySQL version: 8.0

Answer 1

So you want the field tbl3.t3key for rows where tbl3.t3id matches tbl2.anotherid and tbl2.id = tbl1.id?

You can't do that with subqueries, but just about every subquery can be written using joins instead and doing so makes all of the values in those "lower" tables available to your "top-level" query, something like this:

SELECT 
  t1.tbl1field
, t3.t3key                     -- field now available to be queried 
FROM tbl1 AS t1 
INNER JOIN tbl2 AS t2 
   ON t1.id = t2.t2id          -- replaces: t1.id = (SELECT t2.t2id ... 
INNER JOIN tbl3 AS t3 
   ON t2.anotherid = t3.t3id   -- replaces: t2.anotherid = (SELECT t3.t3id ...

Also, on a stylistic note, don't prefix field names with the table name.
It's redundant and you can always add an alias in your query to distinguish, say, one "id" column from another (not that you should ever "leak" those id values outside your database, but that's a whole other story).