Re: [sqlite] Select on foreign key NULL

[Leo Freitag]

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

Re: [sqlite] Select on foreign key NULL

[John Machin]

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] Select on foreign key NULL

[Leo Freitag]

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 |

Thanks in advance
Leo

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select on foreign key NULL

[Simon Davies]

2009/5/26 Leo Freitag :
> 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