Selecting multiple columns/fields in MySQL subquery

Question

Basically, there is an attribute table and translation table - many translations for one attribute.

I need to select id and value from translation for each attribute in a specified language, even if there is no translation record in that language. Either I am missing some join technique or join (without involving language table) is not working here since the following do not return attributes with non-existing translations in the specified language.

select a.attribute, at.id, at.translation 
from attribute a left join attributeTranslation at on a.id=at.attribute
where al.language=1;

So I am using subqueries like this, problem here is making two subqueries to the same table with the same parameters (feels like performance drain unless MySQL groups those, which I doubt since it makes you do many similar subqueries)

select attribute, 
(select id from attributeTranslation where attribute=a.id and language=1),
(select translation from attributeTranslation where attribute=a.id and language=1), 
from attribute a;

I would like to be able to get id and translation from one query, so I concat columns and get the id from string later, which is at least making single subquery but still not looking right.

select attribute,
(select concat(id,';',title)
    from offerAttribute_language 
    where offerAttribute=a.id and _language=1
)
from offerAttribute a

So the question part. Is there a way to get multiple columns from a single subquery or should I use two subqueries (MySQL is smart enough to group them?) or is joining the following way to go:

[[attribute to language] to translation] (joining 3 tables seems like a worse performance than subquery).

Answer 1

Yes, you can do this. The knack you need is the concept that there are two ways of getting tables out of the table server. One way is ..

FROM TABLE A

The other way is

FROM (SELECT col as name1, col2 as name2 FROM ...) B

Notice that the select clause and the parentheses around it are a table, a virtual table.

So, using your second code example (I am guessing at the columns you are hoping to retrieve here):

SELECT a.attr, b.id, b.trans, b.lang
FROM attribute a
JOIN (
 SELECT at.id AS id, at.translation AS trans, at.language AS lang, a.attribute
 FROM attributeTranslation at
) b ON (a.id = b.attribute AND b.lang = 1)

Notice that your real table attribute is the first table in this join, and that this virtual table I've called b is the second table.

This technique comes in especially handy when the virtual table is a summary table of some kind. e.g.

SELECT a.attr, b.id, b.trans, b.lang, c.langcount
FROM attribute a
JOIN (
 SELECT at.id AS id, at.translation AS trans, at.language AS lang, at.attribute
 FROM attributeTranslation at
) b ON (a.id = b.attribute AND b.lang = 1)
JOIN (
 SELECT count(*) AS langcount,  at.attribute
 FROM attributeTranslation at
 GROUP BY at.attribute
) c ON (a.id = c.attribute)

See how that goes? You've generated a virtual table c containing two columns, joined it to the other two, used one of the columns for the ON clause, and returned the other as a column in your result set.