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
