Sorry, i was too fast.
Correction
SELECT engine,coalesce(groupname,'*') as
groupname,databasename,key,value FROM EnginePreferences left join groups
on (groups.groupid = EnginePreferences.groupid);
OR
SELECT engine,groupname,databasename,key,value
FROM EnginePreferences
left join (select groupid,groupname from groups
union
select 0, '*') g_helper on ( g_helper.groupid =
EnginePreferences.groupid);
Martin
Am 23.03.2015 um 10:04 schrieb Martin Engelschalk:
> Hi,
>
> SELECT engine,coalesce(groupname,*) as
> groupname,databasename,key,value FROM EnginePreferences left join
> groups on (groups.groupid = EnginePreferences.groupid);
>
> OR
>
> SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value
> FROM EnginePreferences
> left join (select groupid,groupname from groups
> union
> select 0, '*') g_helper on ( g_helper.groupid =
> EnginePreferences.groupid);
>
> HTH
> Martin
>
> Am 23.03.2015 um 09:50 schrieb Marco Bambini:
>> I have a table EnginePreference:
>> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE,
>> databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value
>> TEXT, groupid INTEGER, UNIQUE(engine,databasename,key))
>>
>> and a table Groups:
>> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT
>> UNIQUE COLLATE NOCASE)
>>
>> I need to select from EnginePreferences replacing groupid with
>> groupname and I can do that with:
>> SELECT engine,groupname,databasename,key,value FROM EnginePreferences
>> left join groups on (groups.groupid = EnginePreferences.groupid);
>>
>> What I really need is ALSO to replace groupname with * if groupid is 0.
>> Please note that groupid 0 is never written into the Groups table so
>> my original query would return NULL as groupname with groupid is 0
>> instead of the required "*".
>>
>> Any idea?
>> --
>> Marco Bambini
>> http://www.sqlabs.com
>> http://twitter.com/sqlabs
>> http://instagram.com/sqlabs
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
--
*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at
Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309
