Empty result SQL query.

Question

0.00/5 (No votes)

See more:

Hi,
I'm trying to retreive the StudentID, FirstName, LastName and CGPA from the table std_details this based on the condition that the student takes a set module. Here is the querry that I designed unfortunately it return and empty set:

SQL

SELECT 
	std_details.StudentID, 
	std_details.FirstName, 
	std_details.LastName, 
	std_details.CGPA 
FROM std_details 
INNER JOIN csr_courses 
	ON std_details.StudentID = csr_courses.StudentID 
WHERE csr_courses.Course_1 = 'PHIL340' 
OR csr_courses.Course_2 = 'PHIL340' 
OR csr_courses.Course_3 = 'PHIL340' 
OR csr_courses.Course_4 = 'PHIL340' 
OR csr_courses.Course_5 = 'PHIL340'
;

I'm not sure I'm doing the Join right, I'm quite new to sql.

Here is some sample data for the csr table:
http://hpics.li/bd9a863

and fort the std table:
http://hpics.li/13384e0

The exact error code is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN csr_courses ON std_details.StudentID = csr_courses.StudentIDWHERE csr' at line 1

Posted 19-Dec-14 3:34am

Member 11322545

Updated 19-Dec-14 4:23am

v5

Add a Solution

Comments

jaket-cp 19-Dec-14 9:51am

The query looks okay.
What values do you have in table std_details and csr_courses?
What result do you expect?
Also looking at the query, you may want to look into normalizing your database design.

PIEBALDconsult 19-Dec-14 9:53am

Some sample data that you think should be returned might be helpful.

Afzaal Ahmad Zeeshan 19-Dec-14 10:09am

Hopefully, and I am pretty much sure that either table is empty, or there is no match for these values.

Member 11322545 19-Dec-14 10:13am

in std_details I have the following:
StudentID, FirstName, LastName, initial, city, state, phone, gender, year, major, credits, cgpa
And in csr_courses I have:
StudentID, Course_1, Grade_1, CourseCredit_1 and these 3 columns up to 5
I've added pictures of my sample data

PIEBALDconsult 19-Dec-14 10:32am

Oh, a syntax error, you should have said so. Perhaps it's the semi-colon?

Member 11322545 19-Dec-14 10:36am

the semicolon is suppose to terminate the query I though that was standard

Marcin Kozub 19-Dec-14 11:26am

I'm not sure but I think the problem is missing space here -> StudentIDWHERE (between StudentID and WHERE). Can you confirm that? It can happen if you're inserting query in string inside code.

Member 11322545 19-Dec-14 11:31am

no this is not it :/

Jörgen Andersson 19-Dec-14 15:37pm

Consider normalizing your tables, then this wouldn't have been a problem.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-12-19T06:23:00

Solution 1

You need to use aliases + RIGHT JOIN, which means: get all records from csr_courses and matching data from std_details:

SQL

SELECT sd.StudentID, sd.FirstName, sd.LastName, sd.CGPA
FROM std_details sd RIGHT JOIN csr_courses cc ON sd.StudentID = cc.StudentID
WHERE cc.Course_1 = 'PHIL340' OR cc.Course_2 = 'PHIL340' OR cc.Course_3 = 'PHIL340' OR cc.Course_4 = 'PHIL340' OR cc.Course_5 = 'PHIL340'

For further information, please see:
Visual Representation of SQL Joins[^]
MySQL JOIN[^]

By The Way: Are you sure that csr_courses contains searched data in Course_x field?

Posted 19-Dec-14 6:23am

Maciej Los

Comments

Member 11322545 19-Dec-14 13:44pm

thank you very much, this know at least gives me NULL values before just wasn't working. And yes I am sure this value is in if you look at the tables (picture link) you can even see the value PHIL340

Member 11322545 19-Dec-14 14:04pm

This solution works for all the values excpet the first one of the table for some reason, it's ok its good enough for today !

Maciej Los 19-Dec-14 18:11pm

You're welcome ;)