Basically there is attribute table and translation table - many translations for one attribute.
I need to select id and value from translation for each attribute in 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 following do not return attributes with non existing translations in specified language.
select a.attribute, at.id, at.translation
from attribute a left join attributeTranslation at on a.id=at.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;
where offerAttribute=a.id and _language=1
from offerAttribute a
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 name 2 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.