- Joined
- 4 Jan 2013
- Posts
- 3,783
SELECT registration.coursecode, ICTnetwork.studentnumber, ICTnetwork.networkusername, ICTnetwork.networkpass, student.surname
FROM ICTnetwork
JOIN Student ON Student.studentnumber = ICTnetwork.studentnumber
JOIN Registration ON Registration.studentnumber = Student.studentnumber
WHERE registration.coursecode = 'BBS'
ORDER BY student.surname DESC;
Line5: "JOIN student.studentnumber"
should be: "JOIN Student"
Is ICTnetwork.studentnumber a real field? I was just guessing so you might need to change that part as well.
FROM
ICTNetwork
JOIN student ON student.studentnumber = ICTnetwork.studentnumber
JOIN registration ON registration.studentnumber = student.studentnumber
At the moment, you are trying to select from the 3 tables and then also perform 2 joins on non-existant tables. Remove the tables you are joining to from the "FROM" clause and then sort out the joins by referencing the table and not the field. EG:
Code:FROM ICTNetwork JOIN student ON student.studentnumber = ICTnetwork.studentnumber JOIN registration ON registration.studentnumber = student.studentnumber
The join you had on "registration.studentnumber" would typically mean a database/schema called "registration" and then a table called "studentnumber".
The way you had it setup originally when all 3 tables were in the "FROM" clause would usually be called a "cross join" which means join all rows in one table to all rows in another and generates something called a Cartesian Product.
"STUDENTNUMBER"."ICTNETWORK": invalid identifier
Do you have a table called "studentnumber"?
You might want to post the whole query as there was no mention of "studentnumber.ictnetwork" in the bit I posted.
Select registration.coursecode, ICTnetwork.studentnumber, ICTnetwork.networkusername, ICTnetwork.networkpassword, student.surname
FROM registration, ICTnetwork, student
JOIN registration
ON student.studentnumber = registration.studentnumber
JOIN student
ON student.studentnumber = ICTNetwork.student
WHERE registration.coursecode=’BBS’
ORDER BY student.surname DESC;
Select
registration.coursecode,
ICTnetwork.studentnumber,
ICTnetwork.networkusername,
ICTnetwork.networkpassword,
student.surname
FROM
ICTnetwork
JOIN student ON student.studentnumber = ICTNetwork.student[COLOR="Red"]number[/COLOR]
JOIN registration ON student.studentnumber = registration.studentnumber
WHERE
registration.coursecode = ’BBS’
ORDER BY
student.surname DESC;
You're a saint! I really really appreciate you giving me the help on a Sunday afternoon.
SQLplus is a command line interface which will treat the carriage returns as an execute instruction. If you use a GUI such as SQL Developer (free) you can paste in a whole formatted block and it will be fine.
His code is word-for-word the same as what i posted in #3.
at least you're sorted now tho. Good luck with the other questions
Appreciate the help. If you're around when I need the next 6 queries, you're also welcome to the GTA pack!