Toine H Toine H - 4 months ago 15
PHP Question

Selecting rows from table based on resultset

I've been trying to work with languages in databases for a little while, but this one has me stumped.

So here's the simplified structure of two tables

DATA
descID | descOriginal | deptID | Other Data
-----------|--------------|------- |-----------
10 | TshirtsNL | 1 | ...
20 | TrousersNL | 1 | ...
30 | ShoesNL | 1 | ...


and

LANG
descID |descTranslated| langID
-----------|--------------|-------
10 | TshirtsDE | 1
10 | TshirtsFR | 2
10 | TshirtsEN | 3


So basically, the original description sits in the first table, along with other needed data. However, the translated description sits in another table for when the original description needs to be translated.

To complicate things further: Not all rows in the LANG table have been filled to correspond with the data in the DATA table (this only happens when the customer fills in their translation). This means I can't rely on a simple
JOIN WHERE l.descID = d.descID
.

I've been trying different kinds of joins and coalesce, but I can't seem to make it work.

Below is not supported in my Firebird version (1.5), but might work if your Database supports derived tables.

I thought something like this could work:

SELECT COALESCE(lang.descTranslated, data.descOriginal) AS desc
FROM
(SELECT descID, descOriginal FROM data WHERE deptID =
:deptID) data
LEFT JOIN
(SELECT descID, descTranslated FROM lang) lang
ON
data.descID = lang.descID


But Firebird doesn't seem to like these kind of statements (or I'm missing something), because the following test SQL throws an error "unknown token SELECT"

SELECT * FROM (SELECT descID FROM data)

Answer Source

As already commented by Val Marinov, derived tables were introduced in Firebird 2.0 (in 2006). However for your problem, you don't need to use derived tables:

To get the result you want:

select coalesce(lang.desctranslated, data.descoriginal)
from data 
left join lang
  on data.descid = lang.descid

is sufficient. If you want to specify a specific language, then using the following would suffice:

select coalesce(lang.desctranslated, data.descoriginal)
from data 
left join lang
  on data.descid = lang.descid
where lang.langid = 2 or lang.langid is null

or pushing the condition down to the join:

select coalesce(lang.desctranslated, data.descoriginal)
from data 
left join lang
  on data.descid = lang.descid and lang.langid = 2

I have tested this with Firebird 1.5.6 and the sample data from your question.