Thanks a lot. Both works fine.
Leo
John Machin schrieb:
> On 27/05/2009 1:09 AM, Leo Freitag wrote:
>
>> Hallo,
>>
>> I got some problems with a select on a foreign key with value null.
>> I want to filter all male singers.
>>
>> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT,
>> 'fkvoice' INTEGER, 'sex' TEXT);
>> INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
>> INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
>> INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
>> INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
>> INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');
>>
>> CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
>> INSERT INTO "tblvoice" VALUES(1,'sopran');
>> INSERT INTO "tblvoice" VALUES(2,'alt');
>> INSERT INTO "tblvoice" VALUES(3,'tenor');
>> INSERT INTO "tblvoice" VALUES(4,'bass');
>>
>> SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger,
>> tblvoice
>> WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;
>>
>> -- Result
>>
>> Luciano Pavarotti | m | tenor
>> Robert Lloyd | m | bass
>>
>> -- How do I have to modify the select statement to get the result below:
>>
>> Luciano Pavarotti | m | tenor
>> Robert Lloyd | m | bass
>> Robby Williams | m |
>>
>
> sqlite> select s.name, s.sex, v.voice from tblsinger s left outer join
> tblvoice v on s.fkvoice = v.id where s.sex = 'm';
> Luciano Pavarotti|m|tenor
> Robert Lloyd|m|bass
> Robby Williams|m|
>
> With "visible NULL":
>
> sqlite> select s.name, s.sex, ifnull(v.voice, 'UNKNOWN') from tblsinger
> s left outer join tblvoice v on s.fkvoice = v.id where s.sex = 'm';
> Luciano Pavarotti|m|tenor
> Robert Lloyd|m|bass
> Robby Williams|m|UNKNOWN
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
