sqlite: SQLite dynamic aliases

lundi 23 février 2015

I'd like to define a dynamic alias on sqlite (in order to have the most generic SQL query I could).

I have two tables t1 & t2

t1 structure:


id|url|type

t2 structure:


id|us|es|fr|de|nl

My request is as follows:


SELECT t1.*,us as name FROM t1 INNER JOIN t2 ON (t1.id = t2.id) ORDER BY t1.id ASC;

What I want to do, is to have an alias (eg: name) that gives me the correct value of t2.us matching the id, except if the type id 1.

Eg, I have two entries in each table:


id|url|type
1|http://ift.tt/1Emrk0e
2|http://file/tmp/file.txt|1

And for t2:


id|us|es|fr|de|nl
1|love|amar|amour|die liebe|liefde
2|sugar|azucar|sucre|zucker|suiker

I want to have something like this:


id|url|type|name
1|http://ift.tt/1EmrlB8
2|http://file/tmp/file/txt|1|file/tmp/file/txt

In case of type is 0, do a research in the t2 table. In case of type is 1, simply put the "url" field as name.

Don't really know if it's clear, and if it's possible with SQLite. I'd like to avoid doing this programmatically (in C).

Thanks

sqlite