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.