SQL query help (getting 1200 results from 20 size!)

[sports_brah]

I'm trying to query a database where there are 20 "registered students".
However the output as a snippet below shows, is 1200 in size!

I'm sure it is a basic error, but would one of you be able to help!


Thanks!



edit: each surname seems to be repeated 60?

 

[sports_brah]

****! Knew I'd forgotten something!

Thank you guys, I'll have a poke around with those inner joins and see if I can improve it somewhat!

 

[sports_brah]

The top query gives the broken 1,200 line one.


The second one doesn't work, claiming:



edit this one

 

[sports_brah]

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.

Ahh, I'll have a play around now.

P.s here is a visual representation of everything.





edit: even changing it to student or studentnumber, doesn't work, it gives the same error.

 

[sports_brah]

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:

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.

I tried that, but it now says:

ERROR at line 6:
ORA-00904: "STUDENTNUMBER"."ICTNETWORK": invalid identifier

 

[sports_brah]

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.

My whole query at the moment is:

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;

(Thank you for taking the time to help me, I'm out of my depth here with an assessment very soon!)

 

[sports_brah]

Strangely enough, when I tried that it threw up error codes all over the place.




But when I got rid of the spacings it worked perfectly! SQL is a strange beast! (using SQLplus with oracle express developer).

You're a saint! I really really appreciate you giving me the help on a Sunday afternoon. Got 6 more queries to do for my "homework", and will be having a test on it in the next two days! (if you're around, and can help with some queries, you're more than welcome to my steam pass that I've currently got for same in the Members market).

 

[sports_brah]

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.

SQL developer? Going to have to find that! Thanks!

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

: ((((

I tried yours but it threw up errors. I tried it again now (changing the password name) and it worked fine.

Thank you as well touch, for putting it more succinctly than anyone else.

Appreciate the help. If you're around when I need the next 6 queries, you're also welcome to the GTA pack!