[newbie] sql : simple select question

Hi, My SQL lessons are very far :(

CREATE TABLE CONTACTS
(ID INT,
NAME VARCHAR(10)
PHONE VARCHAR(10))

CREATE TABLE CLASS
(CONTACT INT,
CAT INT)

INSERT INTO CONTACTS VALUES (101, 'toto', '3545')
INSERT INTO CONTACTS VALUES (102, 'pif', '8421')
INSERT INTO CONTACTS VALUES (103, 'loulou', '1245')

INSERT INTO CLASS VALUES (101, 1)
INSERT INTO CLASS VALUES (101, 2)
INSERT INTO CLASS VALUES (102, 1)

toto owns to category 1 and 2
pif owns to category 1
loulou owns to none categories

=> how can I display contacts who own to none categories (like loulou) ?

I tried:
SELECT * FROM contacts LEFT OUTER JOIN class ON id = contact
I get the list of all contacts...
but if I add 'WHERE cat = NULL' : there's no result :(

Thanx for your help !

Re: [newbie] sql : simple select question

"Tony Louazé" wrote in message
news:451bbcfc$0$17446$79c14f64@nan-newsreader-07.noos.net...
> Hi, My SQL lessons are very far :(
>
> CREATE TABLE CONTACTS
> (ID INT,
> NAME VARCHAR(10)
> PHONE VARCHAR(10))
>
> CREATE TABLE CLASS
> (CONTACT INT,
> CAT INT)
>
> INSERT INTO CONTACTS VALUES (101, 'toto', '3545')
> INSERT INTO CONTACTS VALUES (102, 'pif', '8421')
> INSERT INTO CONTACTS VALUES (103, 'loulou', '1245')
>
> INSERT INTO CLASS VALUES (101, 1)
> INSERT INTO CLASS VALUES (101, 2)
> INSERT INTO CLASS VALUES (102, 1)
>
> toto owns to category 1 and 2
> pif owns to category 1
> loulou owns to none categories
>
> => how can I display contacts who own to none categories (like loulou) ?
>
> I tried:
> SELECT * FROM contacts LEFT OUTER JOIN class ON id = contact
> I get the list of all contacts...
> but if I add 'WHERE cat = NULL' : there's no result :(
>
> Thanx for your help !
>
>

isn't that what you'd expect? there don't seem to be any null cats in what
you show....

Re: [newbie] sql : simple select question

"Johnny" wrote in message
news:MATSg.464$UJ2.159@fed1read07...
>
> "Tony Louazé" wrote in message
> news:451bbcfc$0$17446$79c14f64@nan-newsreader-07.noos.net...
> > Hi, My SQL lessons are very far :(
> >
> > CREATE TABLE CONTACTS
> > (ID INT,
> > NAME VARCHAR(10)
> > PHONE VARCHAR(10))
> >
> > CREATE TABLE CLASS
> > (CONTACT INT,
> > CAT INT)
> >
> > INSERT INTO CONTACTS VALUES (101, 'toto', '3545')
> > INSERT INTO CONTACTS VALUES (102, 'pif', '8421')
> > INSERT INTO CONTACTS VALUES (103, 'loulou', '1245')
> >
> > INSERT INTO CLASS VALUES (101, 1)
> > INSERT INTO CLASS VALUES (101, 2)
> > INSERT INTO CLASS VALUES (102, 1)
> >
> > toto owns to category 1 and 2
> > pif owns to category 1
> > loulou owns to none categories
> >
> > => how can I display contacts who own to none categories (like loulou) ?
> >
> > I tried:
> > SELECT * FROM contacts LEFT OUTER JOIN class ON id = contact
> > I get the list of all contacts...
> > but if I add 'WHERE cat = NULL' : there's no result :(
> >
> > Thanx for your help !
> >
> >
>
> isn't that what you'd expect? there don't seem to be any null cats in what
> you show....
>
>

oops! not enough coffee yet! wait one :-)

Re: [newbie] sql : simple select question

"Johnny" wrote in message
news:YTTSg.465$UJ2.42@fed1read07...
>
> "Johnny" wrote in message
> news:MATSg.464$UJ2.159@fed1read07...
> >
> > "Tony Louazé" wrote in message
> > news:451bbcfc$0$17446$79c14f64@nan-newsreader-07.noos.net...
> > > Hi, My SQL lessons are very far :(
> > >
> > > CREATE TABLE CONTACTS
> > > (ID INT,
> > > NAME VARCHAR(10)
> > > PHONE VARCHAR(10))
> > >
> > > CREATE TABLE CLASS
> > > (CONTACT INT,
> > > CAT INT)
> > >
> > > INSERT INTO CONTACTS VALUES (101, 'toto', '3545')
> > > INSERT INTO CONTACTS VALUES (102, 'pif', '8421')
> > > INSERT INTO CONTACTS VALUES (103, 'loulou', '1245')
> > >
> > > INSERT INTO CLASS VALUES (101, 1)
> > > INSERT INTO CLASS VALUES (101, 2)
> > > INSERT INTO CLASS VALUES (102, 1)
> > >
> > > toto owns to category 1 and 2
> > > pif owns to category 1
> > > loulou owns to none categories
> > >
> > > => how can I display contacts who own to none categories (like loulou)
?
> > >
> > > I tried:
> > > SELECT * FROM contacts LEFT OUTER JOIN class ON id = contact
> > > I get the list of all contacts...
> > > but if I add 'WHERE cat = NULL' : there's no result :(
> > >
> > > Thanx for your help !
> > >
> > >
> >
> > isn't that what you'd expect? there don't seem to be any null cats in
what
> > you show....
> >
> >
>
> oops! not enough coffee yet! wait one :-)
>

try
IS NULL
in place of
= NULL

testing for null is a special case

Re: [newbie] sql : simple select question

thanx it works !
I thought and I think there's surely another way to get the same result
(without testing a NULL value)

>> > "Tony Louazé" wrote :
>> > > Hi, My SQL lessons are very far :(
>> > >
>> > > CREATE TABLE CONTACTS
>> > > (ID INT,
>> > > NAME VARCHAR(10)
>> > > PHONE VARCHAR(10))
>> > >
>> > > CREATE TABLE CLASS
>> > > (CONTACT INT,
>> > > CAT INT)
>> > >
>> > > INSERT INTO CONTACTS VALUES (101, 'toto', '3545')
>> > > INSERT INTO CONTACTS VALUES (102, 'pif', '8421')
>> > > INSERT INTO CONTACTS VALUES (103, 'loulou', '1245')
>> > >
>> > > INSERT INTO CLASS VALUES (101, 1)
>> > > INSERT INTO CLASS VALUES (101, 2)
>> > > INSERT INTO CLASS VALUES (102, 1)
>> > >
>> > > toto owns to category 1 and 2
>> > > pif owns to category 1
>> > > loulou owns to none categories
>> > >
>> > > => how can I display contacts who own to none categories (like
>> > > loulou)
> ?
>> > >
>> > > I tried:
>> > > SELECT * FROM contacts LEFT OUTER JOIN class ON id = contact
>> > > I get the list of all contacts...
>> > > but if I add 'WHERE cat = NULL' : there's no result :(
>> > >
>> > > Thanx for your help !
>> > >
>> > >
>> >
>> > isn't that what you'd expect? there don't seem to be any null cats in
> what
>> > you show....
>> >
>> >
>>
>> oops! not enough coffee yet! wait one :-)
>>
>
> try
> IS NULL
> in place of
> = NULL
>
> testing for null is a special case
>
>