> 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
