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

sports_brah · 24 Nov 2013 at 14:46

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?

Redrum · 24 Nov 2013 at 15:29

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.

touch · 24 Nov 2013 at 15:35

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

sports_brah · 24 Nov 2013 at 15:38

****! 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 · 24 Nov 2013 at 16:52

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

The second one doesn't work, claiming:

edit this one

touch · 24 Nov 2013 at 17:02

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.

sports_brah · 24 Nov 2013 at 17:09

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.

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.

RobH · 24 Nov 2013 at 17:11

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.

sports_brah · 24 Nov 2013 at 17:15

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.

I tried that, but it now says:

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

RobH · 24 Nov 2013 at 17:17

sports_brah said:
"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.

sports_brah · 24 Nov 2013 at 17:21

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.

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

RobH · 24 Nov 2013 at 17:29

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".

sports_brah · 24 Nov 2013 at 17:38

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

RobH · 24 Nov 2013 at 17:48

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.

touch · 24 Nov 2013 at 18:00

sports_brah said:
You're a saint! I really really appreciate you giving me the help on a Sunday afternoon.

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

sports_brah · 24 Nov 2013 at 18:07

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.

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.

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!

touch · 24 Nov 2013 at 18:14

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

Haha, i'm just joking. I dont have a steam account or play any other games.

Let us know if you need help with the others tho.

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

Thug

Thug

Thug

Thug

Thug

Thug

Thug

Thug