Problem with equi join and repeating data.

Im having troubles with a CD rental database

I have 5 tables in my sql:
Members1 (containing members details and a member ID),

Rentalhead1 ( containing RentalNumber and MemberID along with DateOfRental and StaffName)

RentalLine (containing RentalNumber, StockID and ReturnDate)

Stock1 (containing StockID, CatalogueNumber, Purchase Cost and PurchaseDate)

And finally CD1 which contains CatalogueNumber,Title(of CD) and Daily cost.

All tables are linked correctly*


I have produced the following code to display: Surname, FirstName, CD, RentalDate, ReturnDate

SELECT Distinct surname, firstname, TITLE, DATEOFRENTAL, returndate
FROM RENTALLINE1, MEMBER1, CD1, RENTALHEAD1, STOCK1
WHERE MEMBER1.MEMBERID = RENTALHEAD1.MEMBERID
AND RENTALHEAD1.RENTALNUMBER = RENTALLINE1.RENTALNUMBER
AND RENTALLINE1.STOCKID = STOCK1.STOCKID
AND STOCK1.CATALOGUENUMBER = CD1.CATALOGUENUMBER
ORDER BY SURNAME

The result of this code is members having cds lent out to them which they havent rented and incorrect rental and return dates... Im slightly baffled. please help.

I think the problem may be the links i have constructed but im not too sure!

the fields you selected is that from one table.
When selecting fields from more than one table you have to use the table name as <tablename>.<fieldname>
ex. Members1.surname

think you should have a look at the JOIN command
http://www.w3schools.com/sql/sql_join.asp

your joins do look okay to me

maybe it's bad data

well surname, firstname are from the MEMBER1 Table, TITLE is from the CD TABLE, DATEOFRENTAL is from RENTALHEAD1 and RETURNDATE is from RENTALLINE1.



The following is sample data for one member, the highlighted record is correct: *note its all test data*

BATHE PETER Dire Straights Greatest Hits 21-APR-99 26-APR-99

BATHE PETER Solo Piano Greats 28-MAY-99 02-JUN-99

BATHE PETER Worlds Greatest Opera Arias 15-APR-99 20-APR-99

and you're saying that BATHE PETER didn't rent those other two unbolded ones?

yeah, there should only be one record the highlighted one i have printed out all the tables and confirmed this..

so why did those other rows get returned?

it can only be because of the values in the ID columns that relate the rows across the various tables

this may help you track down the problem --

FROM RENTALLINE1 as l
*

ERROR at line 16:
ORA-00933: SQL command not properly ended


It didnt like that, is this code simpler to read and perform the same operation as my attempt?

you posted an oracle question in the mysql forum????

(not that it should make any difference, because the sql is standard)

should i take my problem else where?

i'm also a forum moderator, i will move it for you

try removing the AS keywords

Ok thanks for the help, sorry for the mess up