Need Help with SQL Query

[Dazza203]

Hi all,

I am trying to do an SQL query involving these 2 tables:

CREATE TABLE Student (
StudentNo VARCHAR2(9) NOT NULL,
StudentType VARCHAR2(13),
FirstName VARCHAR2(20),
SecondName VARCHAR2(20),
Sex VARCHAR2(6),
DateOfBirth DATE,
HomeSteet VARCHAR2(20),
HomeTown VARCHAR2(15),
HomeCity VARCHAR2(15),
HomePostCode VARCHAR2(8),
HomeTeleNo CHAR(15),
ProgramOfStudy VARCHAR2(30),
DepartmentName VARCHAR2(30),
Year VARCHAR2(3),
YearTutor VARCHAR2(20),
Supervisor VARCHAR2(20),
Disabled VARCHAR2(3),
NextOfKinNo VARCHAR2(9) NOT NULL,
CONSTRAINT Student_PRIMARY_KEY PRIMARY KEY (StudentNo));

CREATE TABLE WaitingList (
StudentNo VARCHAR2(9) NOT NULL,
CurrentStatus VARCHAR2(7),
CONSTRAINT CurrentStatus_PRIMARY_KEY PRIMARY KEY (StudentNo));

The query needs to pick out the Students who have the CurrentStatus attribute in the WaitingList field set at 'Waiting' and for those that do, display the value of their 'Name','HomeStreet','HomeTown','HomeCity','HomePostCode', and 'DepartmentName' attributes from the Student table. Would that mean that I would also have to create some sort of constraint between the 'StudentNumber' attribute on the WaitingList table and 'StudentNumber' attribute on the Student table? They are the Primary Key on both tales. So far I have come up with this:

SELECT * FROM WaitingList WHERE CurrentStatus = 'Waiting';

All this does is display the values of the 'StudentNumber' and 'CurrentStatus' attributes of the WaitingList table whose 'CurrentStatus' has the value 'Waiting'.

Thanks,
Darren

 

[bdreed35]

Try this:

SELECT (FirstName + ' ' + SecondName) as Name,
       HomeSteet,
       HomeTown,
       HomeCity,
       HomePostCode,
       DepartmentName
FROM Student
 INNER JOIN WaitingList 
  ON Student.StudentNo = WaitingList.StudentNo
WHERE WaitingList.CurrentStatus = 'Waiting'

~Brian

 

[swampBoogie]

select Name,HomeStreet,HomeTown,HomeCity,HomePostCode
from students 
where StudentNumber in
(select StudentNumber
from WaitingList 
WHERE CurrentStatus = 'Waiting')

You could add a foreign key to the WaitingList table.

 

[Dazza203]

SwampBoogie's Code did not work so I have tried various different versions of bdreed35's, but whatever I try I get errors. Here is what I have now:

SELECT 'FirstName',
'SecondName',
'HomeSteet',
'HomeTown',
'HomeCity',
'HomePostCode',
'DepartmentName',
FROM Student
INNER JOIN WaitingList
ON Student.StudentNumber = WaitingList.StudentNumber
WHERE WaitingList.CurrentStatus = 'Waiting';

But it give this error is Oracle:

ERROR at line 8:
ORA-00936: missing expression

Has anyone got any ideas?

Thanks,
Darren

 

[carp]

What version of Oracle are you using?

 

[Dazza203]

I have now figured out the error from my last post. Here is the newest code:

SELECT 'FirstName',
'SecondName',
'HomeSteet',
'HomeTown',
'HomeCity',
'HomePostCode',
'DepartmentName'
FROM Student
INNER JOIN WaitingList
ON Student.StudentNumber = WaitingList.StudentNumber
WHERE WaitingList.CurrentStatus = 'Waiting';

It produces this error:

ERROR at line 10:
ORA-00904: "WAITINGLIST"."STUDENTNUMBER": invalid identifier

Anyone got any ideas?

Thanks,
Darren

 

[Dazza203]

I am using Oracle 9i.

Darren

 

[carp]

The only things that come to mind are (1) there is no WAITINGLIST table, (2) there is no STUDENTNUMBER column in the WAITINGLIST table, or (3) you cannot access the WAITINGLIST table via the method you are using.

You might try changing your code to fully qualify the table (e.g., SCOTT.WAITINGLIST.STUDENTNUMBER, and grant select on the table to public. If that takes care of the problem, then it was an access problem.

 

[Dazza203]

It's OK I have got it to work now, all I needed to do was change the StudentNumber to StudentNo because that it that I called that attribute when I created the tables?

Thanks,
Darren

 

[bdreed35]

In your first post your field in both your tables was StudentNo, not StudentNumber.
Try this:

SELECT     'FirstName',
    'SecondName',
    'HomeSteet',
    'HomeTown',
    'HomeCity',
    'HomePostCode',
    'DepartmentName'
FROM Student
INNER JOIN WaitingList 
ON Student.StudentNo = WaitingList.StudentNo
WHERE WaitingList.CurrentStatus = 'Waiting';

~Brian

 

[Dazza203]

Yes its OK now I did what you just suggested. Read my post above. Here is the code for all my tables in this database:

CREATE TABLE Student (
StudentNo VARCHAR2(9) NOT NULL,
StudentType VARCHAR2(13),
FirstName VARCHAR2(20),
SecondName VARCHAR2(20),
Sex VARCHAR2(6),
DateOfBirth DATE,
HomeSteet VARCHAR2(20),
HomeTown VARCHAR2(15),
HomeCity VARCHAR2(15),
HomePostCode VARCHAR2(8),
HomeTeleNo CHAR(15),
ProgramOfStudy VARCHAR2(30),
DepartmentName VARCHAR2(30),
Year VARCHAR2(3),
YearTutor VARCHAR2(20),
Supervisor VARCHAR2(20),
Disabled VARCHAR2(3),
NextOfKinNo VARCHAR2(9) NOT NULL,
CONSTRAINT Student_PRIMARY_KEY PRIMARY KEY (StudentNo));

CREATE TABLE WaitingList (
StudentNo VARCHAR2(9) NOT NULL,
CurrentStatus VARCHAR2(7),
CONSTRAINT CurrentStatus_PRIMARY_KEY PRIMARY KEY (StudentNo));

CREATE TABLE RentalAgreement (
LeaseNo VARCHAR2(9) NOT NULL,
RentPeriodstart DATE,
RentPeriodFinish DATE,
StudentNo VARCHAR2(9) NOT NULL,
CONSTRAINT RentalAgreement_PRIMARY_KEY PRIMARY KEY (LeaseNo));

CREATE TABLE SingleRoom (
PlaceNo VARCHAR2(9) NOT NULL,
RoomNo VARCHAR2(9),
WeeklyRentRate DECIMAL(4,2),
Disabled VARCHAR2(3),
LeaseNo VARCHAR2(9) NOT NULL,
CONSTRAINT SingleRoom_PRIMARY_KEY PRIMARY KEY (PlaceNo));

CREATE TABLE HallsOfResidence (
HallName VARCHAR2(20) NOT NULL,
Street VARCHAR2(30),
Town VARCHAR2(15),
City VARCHAR2(15),
PostCode VARCHAR2(8),
TeleNo CHAR(15),
PlaceNo VARCHAR2(9) NOT NULL,
CONSTRAINT HallsOfResidence_PRIMARY_KEY PRIMARY KEY (HallName));

CREATE TABLE HallManager (
StaffNo VARCHAR2(9) NOT NULL,
Name VARCHAR2(20),
Street VARCHAR2(20),
Town VARCHAR2(15),
City VARCHAR2(15),
PostCode VARCHAR2(8),
TeleNo CHAR(15),
HallName VARCHAR2(20) NOT NULL,
CONSTRAINT HallManager_PRIMARY_KEY PRIMARY KEY (StaffNo));

CREATE TABLE PrivateLandlord (
PrivateLandlordNo VARCHAR2(9) NOT NULL,
Name VARCHAR2(20),
Street VARCHAR2(20),
Town VARCHAR2(15),
City VARCHAR2(15),
PostCode VARCHAR2(8),
TeleNo CHAR(15),
PropertyStreet VARCHAR2(20),
PropertyTown VARCHAR2(15),
PropertyCity VARCHAR2(15),
PropertyPostCode VARCHAR2(8),
PropertyTeleNo CHAR(15),
WeeklyRentRate DECIMAL(4,2),
RentPeriodStart DATE,
RentPeriodFinish DATE,
StudentNo VARCHAR2(9) NOT NULL,
CONSTRAINT PrivateLandlord_PRIMARY_KEY PRIMARY KEY (PrivateLandlordNo));

CREATE TABLE NextOfKin (
NextOfKinNo VARCHAR2(9),
Name VARCHAR2(20),
Relationship VARCHAR2(15),
Street VARCHAR2(20),
Town VARCHAR2(15),
City VARCHAR2(15),
PostCode VARCHAR2(8),
TeleNo VARCHAR2(15),
StudentNo VARCHAR2(9) NOT NULL,
CONSTRAINT NextOfKin_PRIMARY_KEY PRIMARY KEY (NextofKinNo));

I need to now create query where for a given value of the HallName attribute in the HallsOfResidence table, the values of Name, Relationship, Street, Town, City, PostCode, and TeleNo attribute from the NextOfKin table, and the values of the FirstName, SecondName, and Department attributes of the Student table. I have no clue about this one. Any help?

Thanks,
Darren