2009/5/26 Leo Freitag <leofrei...@netcologne.de>:
> 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 |
Use a left join:
SELECT tblsinger.name, tblsinger.sex, tblvoice.voice
FROM tblsinger left join tblvoice
ON tblsinger.fkvoice = tblvoice.id
WHERE tblsinger.sex = 'm';
>
> Thanks in advance
> Leo
>
Rgds,
Simon
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
