Home > Archive > PHP SQL > September 2006 > [newbie] sql : simple select question
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
[newbie] sql : simple select question
|
|
| Tony Louazé 2006-09-28, 7:58 am |
| 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 !
| |
| Johnny 2006-09-28, 9:57 pm |
|
"Tony Louazé" <tlouaze@noos.fr> 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....
| |
| Johnny 2006-09-28, 9:57 pm |
|
"Johnny" <removethis.huuanito@hotmail.com> wrote in message
news:MATSg.464$UJ2.159@fed1read07...
>
> "Tony Louazé" <tlouaze@noos.fr> wrote in message
> news:451bbcfc$0$17446$79c14f64@nan-newsreader-07.noos.net...
>
> 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 :-)
| |
| Johnny 2006-09-28, 9:57 pm |
|
"Johnny" <removethis.huuanito@hotmail.com> wrote in message
news:YTTSg.465$UJ2.42@fed1read07...
>
> "Johnny" <removethis.huuanito@hotmail.com> wrote in message
> news:MATSg.464$UJ2.159@fed1read07...
?[color=darkred]
what[color=darkred]
>
> oops! not enough coffee yet! wait one :-)
>
try
IS NULL
in place of
= NULL
testing for null is a special case
| |
| Tony Louazé 2006-09-28, 9:57 pm |
| thanx it works !
I thought and I think there's surely another way to get the same result
(without testing a NULL value)
> ?
> what
>
> try
> IS NULL
> in place of
> = NULL
>
> testing for null is a special case
>
>
|
|
|
|
|