Many thanks. Problem is cured
----- Original Message -----
From: "Thalis A. Kalfigopoulos" <[EMAIL PROTECTED]>
To: "Cory Whitesell" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Saturday, February 24, 2001 4:26 PM
Subject: Re: Stumped
> > This should be very simple, but for some reason, my query does not
return the desired results
> > I have the following two tables:
> >
> > CREATE TABLE Rank (
> > Rank CHAR(40) NOT NULL PRIMARY KEY,
> > ReportsTo CHAR(40) NULL
> > );
> >
> > CREATE TABLE ROSTER (
> > EntryNumber INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
> > IDNumber INTEGER NOT NULL,
> > LastName CHAR(30) NOT NULL,
> > FirstName CHAR(30) NOT NULL,
> > DOB Date NULL,
> > DateJoined Date NULL,
> > Rank CHAR(40), NULL
> > CrewName CHAR(30), NULL
> > HomePhone CHAR(20) NULL,
> > WorkPhone CHAR(20) NULL,
> > CellPhone CHAR(20) NULL,
> > PagerNumber CHAR(20) NULL,
> > EMail CHAR(50) NULL,
> > );
> >
> > I want to select members that are not at the root level for rank
(ReportsTo field is not blank), but are also not assigned to a crew
(CrewName is blank)
> >
> > And the query
> >
> > SELECT A.*, B.ReportsTo
> > FROM ROSTER A, RANK B
> > WHERE A.Rank = B.Rank
> > AND ReportsTo <> ''
> > AND CrewName = ''
> >
> > does not return the names of members that I know are not assigned to a
crew.
> >
> > What have I done wrong? One would think that this is very straight
forward.
>
>
> An educated guess:
>
> <QUOTE>
> 20.17 Problems with NULL Values
>
> The concept of the NULL value is a common source of confusion for
newcomers to SQL, who often think that NULL is the same thing as an empty
string ''. This is not the case!
> </QUOTE>
>
> So it should work as:
>
> select a.*,b.reportsto
> from roster a, rank b
> where a.rank=b.rank
> and reportsto IS NOT NULL,
> and crewname IS NULL;
>
> regards,
> thalis
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
