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.
