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

sports_brah

sports_brah

sports_brah

Thug
Soldato
Joined
4 Jan 2013
Posts
3,783
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!

wMdYhkZ.png


edit: each surname seems to be repeated 60?
 
You're selecting data from 3 different tables but you haven't specified how the 3 tables relate to each other, so the DBMS is giving you every combination of every row. You need to learn about joins. I'd suggest starting with W3Schools.
 
You probably need to do a join when selecting from 3 different tables.
Something like:
Code: 
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;
I'm just guessing the studentnumber field might be used to join the registration table. you need to find
 
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.
 
touch said:
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.
Click to expand...

Ahh, I'll have a play around now.

P.s here is a visual representation of everything.

LR2tLT0.png




edit: even changing it to student or studentnumber, doesn't work, it gives the same error.
 
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.
 
Last edited:
RobH said:
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.
Click to expand...


I tried that, but it now says:

ERROR at line 6:
ORA-00904: "STUDENTNUMBER"."ICTNETWORK": invalid identifier
 
RobH said:
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.
Click to expand...

My whole query at the moment is:
Code: 
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!)
 
Give this a go:

Code: 
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;

People have different styles when it comes to formatting SQL, I typically use the one above as it makes it a lot more readable.

You still had references to the join tables in the "FROM" clause and the key reference was incorrect, it should be "studentnumber" instead of "student".
 
Strangely enough, when I tried that it threw up error codes all over the place.

OwxnVqQ.png



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).
 
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.
 
RobH said:
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.
Click to expand...

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


touch said:
:( His code is word-for-word the same as what i posted in #3.

:p at least you're sorted now tho. Good luck with the other questions :D
Click to expand...

: ((((

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. :D

Appreciate the help. If you're around when I need the next 6 queries, you're also welcome to the GTA pack! :D
 
You must log in or register to reply here.
Back
Top Bottom