Problem with Query

[Dazza203]

I want this query to display 3 attributes from 3 different tables depending on the value of an attribute given at the beginning of the query, but I can't get the syntax right for the SELECT clause. this is the latest version:


SELECT StudentName FROM Student,RoomNo FROM SingleRoom,Name FROM HallManager
WHERE HallsOfResidence.HallName &HallName;

This looks like simple stuff but even in my book I can't find anything about this.

Thanks,
Darren

 

[swampBoogie]

If the data is a as unrelated as it looks, use separate queries.

 

[Dazza203]

It has to be in the same query, it just has to include values in attributes from different tables, is there no way of doing this?

Thanks,
Darren

 

[Golom]

There are indeed ways of doing that ... however ...

Please show the structure of each of your tables and the fields that allow you to relate one table to another. A short example of the results that you want to see would also be helpful.

Your SQL shows 3 "From" clauses (not legal as far as I know) and you appear to be referencing at least 6 different tables (or are some of them fields?) .

 

[Dazza203]

OK here is the structure of all the tables that are needed for this query

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 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));

Thanks,
Darren

 

[Dazza203]

It's OK I have now figured out the SELECT clause problem using a tutorial that I found on a website. The problem I have now is with the last line. Here is what I have now:

SELECT Student.StudentName, SingleRoom.RoomNo,HallManager.Name
FROM Student,SingleRoom,HallManager
WHERE HallsOfResidence.HallName = '&HallName';

And this is the error message that I am getting:

ERROR at line 3:
ORA-00904: "HALLSOFRESIDENCE"."HALLNAME": invalid identifier

I guess that would mean that the table or attribute name isn't right but if you look at my tables in my post above they are. Any help?

Thanks,
Darren

 

[r937]

the reason you are getting that error is because it refers to a table column for a table that is not mentioned in the FROM clause

also, you have not given any join conditions to join the tables

with just

FROM Student,SingleRoom,HallManager

and no join conditions, you are going to get each student joined to each room, and each of those combinations joined to every hall manager

this is called a cartesian product or cross join

not what you want

rudy

http://r937.com/

 

[UAMI]

I think you have to add the rentalagreement table to do the link to the rest of the other table's.

Try this:
SELECT Student.StudentName, SingleRoom.RoomNo,HallManager.Name
FROM Student,,RentalAgreement,SingleRoom,HallManager
WHERE Studant.StudantNo=RentalAgreement. StudentNo AND RentalAgreement. LeaseNo=SingleRoom. LeaseNo and SingleRoom. PlaceNo=HallsOfResidence. PlaceNo and HallsOfResidence. HallName=HallManager. HallName;

 

[UAMI]

I think you have to add the rentalagreement table, to make the link with the other table's.
Try this:
SELECT Student.StudentName, SingleRoom.RoomNo,HallManager.Name
FROM Student,,RentalAgreement,SingleRoom,HallManager
WHERE Studant.StudantNo=RentalAgreement. StudentNo AND RentalAgreement. LeaseNo=SingleRoom. LeaseNo and SingleRoom. PlaceNo=HallsOfResidence. PlaceNo and HallsOfResidence. HallName=HallManager. HallName;