show different columns from different table with inner join

Hi i have a lot of inner in a query but i can’t print column in different table, for example: where

Table A
id       f
-----------
xxx     gggg
bbb     kkkk

Table B
name    code
-----------
ccc     dddd
bbb     oooo

My code:

select A.id,A.F from (query1)as stima
join
(select B.name,B.code from B as aaaa query)as noome
on noome.code=stima.F;

But if i want put name columns of B table in ‘select A.id,A.F’ how can i do? My output is id and colum F but i want output: id,F and name columns.

Answer

Any time you have a column in an inner query that you want to use in an outer query, you have to make sure the inner query selects it. It will then become part of the query block with the new alias you give it:

SELECT
  a.Name,
  a.SomethingElse,
  b.ColumnX,
  b.ColumnYYY
FROM
  (
    SELECT c.Name, d.SomethingElse FROM c JOIN d ON ..
  ) a
  JOIN
  (
    SELECT e.ColumnX, f.ColY as ColumnYYY FROM e JOIN f ON ..
  ) b

When they’re in the inner queries the columns are like d.SomethingElse and f.ColY but when they pass outside of those brackets, they get a new alias, because they are part of the data block that is aliased by a or b, so you no longer refer to them as their inner names.. Particularly in the case of f.ColY it was renamed to ColumnYYY and also given a new “table” alias of b..