Selecting rows from table based on resultset

Question

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)

Which version of Firebird? You are using a select in the FROM clause, this is called a derived table, and they was introduced in Firebird 2.0. — Val Marinov, Jul 17 '17 at 09:52
We're using Firebird 1.5, but we can't upgrade because of incompatibilities with certain functions. This does does however explain why derived tables won't work. — Toine H, Jul 17 '17 at 10:27
Firebird 2.0 was released 11 years ago, the last Firebird 1.5 update was 8 years ago. Several security bugs have been discovered and fixed since. You should really consider investing a bit of time to address those incompatibilities to upgrade to Firebird 3.0 (or maybe 2.5) and enjoy the new features introduced in the past 11 years. — Mark Rotteveel, Jul 17 '17 at 11:50
may there be several preferred language? like user would want to see name in German best of all, in Russian if there is no German name, and fallback to English only if neither Russian nor German present. Not a single preferred language, but an ordered list ? — Arioch 'The, Jul 17 '17 at 14:02

score 1 · Accepted Answer · answered Jul 17 '17 at 12:08

1

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.

answered Jul 17 '17 at 12:08

Mark Rotteveel

82,132
136
114
158

Thanks Mark! I didn't even think of using is null, which is what the row would return during a left join. Your second query worked perfectly for me. langid 0 is the standard language (from the DATA table), which didn't work with the query, but was easily remedied within the PHP script (if($langId != 0) { execute your query } else { execute a similar query without joining with the LANG table } BTW I will try to persuade my co-workers to upgrade Firebird ;) – Toine H Jul 17 '17 at 12:45
@ToineH I would expect using `lang.langid = 0 or lang.langid is null` to work just fine. In any case, if my answer helped you solve the problem, then please 'accept' it :) – Mark Rotteveel Jul 17 '17 at 13:01
lang.langid isn't inside the LANG table, but DATA table. That's why lang.langid = 0 didn't work. In any case, thanks again and I've accepted your answer :) – Toine H Jul 17 '17 at 14:27
@ToineH Do you mean `deptid` instead? Your example tables shows `lang` to have `langid` as field. – Mark Rotteveel Jul 17 '17 at 14:30
My brain goofed a bit there, I meant to say that the default language isn't inside the LANG table. Inside our system, the langid is a variable that changes on the selected language. langid 0 isn't inside the LANG table, as that's the default language, which resides in the DATA table. – Toine H Jul 17 '17 at 14:36
@ToineH That was my initial assumption, but I meant that my solution will work even if `0` represents the default language, because as there will be no entries in `lang` for that langid, it will default to `descOriginal`. – Mark Rotteveel Jul 17 '17 at 14:39
@MarkRotteveel I think your first Q here would display ALL available languages for translated data rows, multiplying sets. I guess you have to somehow limit it to one random row. Maybe selecting per-descID MAX(langID) in sub-query. Frankly, i can see no use-case for your first query as is. – Arioch 'The Jul 17 '17 at 14:52
@Arioch'The Yes the first query displays all languages, that was my intention, but so would the (non-working) query from the question. The second part of my answer is what I guessed would be what the OP wanted to have (and I guessed right). – Mark Rotteveel Jul 17 '17 at 14:56
@MarkRotteveel all in all, the recent stream of people (ab)using derived tables instead of directly joining those very tables is terrifying. I wonder what educating material or howtos produced it... – Arioch 'The Jul 17 '17 at 15:00

Arioch 'The · Answer 2 · 2017-07-17T14:55:09.713

1

While Firebird 1.x did not support anonymous derived tables it did support views.

CREATE VIEW DEPT_DESCS AS
  SELECT lang.descID, lang.descTranslated as Dept_Description, lang.langID, languages.lang_name 
  FROM lang 
  LEFT JOIN DATA ON DATA.descID = lang.descID
  JOIN languages ON languages.lang_id = lang.langID
  WHERE DATA.descID is not null -- would not need translations for non-existing lines
  ORDER BY lang.descID, lang.langID DESC
UNION ALL
  SELECT data.descID, data.descOriginal, NULL, NULL FROM data

And now you can select from that view

SELECT first(1) * FROM DEPT_DESCS 
WHERE ( langID in (5,8,10) or langID is NULL )
  AND descID=10
ORDER by langID /* DESC */ NULLS LAST

See https://www.firebirdsql.org/manual/nullguide-sorts.html

One may also use zero or negative number instead of null for non-translated descriptions.

CREATE VIEW DEPT_DESCS AS
  SELECT lang.descID, lang.descTranslated as Dept_Description, lang.langID, languages.lang_name 
  .....
  ORDER BY lang.descID, lang.langID DESC
UNION ALL
  SELECT data.descID, data.descOriginal, -100 /* or 0 */, NULL FROM data

That way the second query gets a bit more simple.

SELECT first(1) * FROM DEPT_DESCS 
WHERE langID in (5,8,10, -100 /* or 0 */ ) 
  AND descID=10
ORDER by langID DESC

However this use of magic constants would obviously demand that

Never any language with the said M.C. (ID = 0 or ID = -100) be actually added, never ever.
The query constructor when forming then in-list would always add the M.C. to it, while in NULLs-based way it would only put there actual values and NULL is accounted for separately, in the query template itself.

edited Jul 17 '17 at 14:55

answered Jul 17 '17 at 14:21

Arioch 'The

15,005
31
59

Interesting, I've accepted Mark's answer, but I might be able to use this in the future. Thanks Arioch! – Toine H Jul 17 '17 at 14:29
@Mark now I wonder. Your 2.5 link says first/skip was introduced in 1.0 but the wiki tracking versions thinks it was introduced as late as 1.5.3 not even 1.5.2 ... I may only wonder... – Arioch 'The Jul 17 '17 at 14:30
@Arioch'The I mostly added the link because having a Russian link is not helpful for most people. But in any case, see the [Firebird 1.0 release notes](https://www.firebirdsql.org/file/documentation/release_notes/Firebird-1.0-ReleaseNotes.pdf), it mentions `first` on page 9. See also the [Firebird 1.5 release notes](https://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes15.html#sql-dml-selectfirst), they only changed something to allow `first 0` – Mark Rotteveel Jul 17 '17 at 14:34
i consulted 1.5.3 relnote - https://www.ibase.ru/files/firebird/fb_1_53_releasenotes.pdf - and seems while First/Skip was significantly enhanced with 1.5 - the enhancement were not relevant for this specific task. I am removing that paragraph now. PS. while text in Russian might be hard to consume - the FB versions table is self-sufficient and i do not think there is any other online documents which document for every language feature its introducing FB version and history.... – Arioch 'The Jul 17 '17 at 14:37
1

@Arioch'The Skip without first and query parameters were also already in Firebird 1, see the release notes of FB 1.0 – Mark Rotteveel Jul 17 '17 at 14:41

Selecting rows from table based on resultset

2 Answers2