Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

Compound SQL Query

I have constructed this SQL query:
SELECT DISTINCT a.uid, a.sid, a.fname, a.lname, b.class_code from student a, class b where b.grade_code = '2' and a.class = b.class_code and a.school_code = 'SES' order by a.class, a.lname, a.fname

My attempt is to pick up all students in grade 2 for school SES. The grade field is not contained in the student table, rather the class field links to class_code in the class table.

The attached files are (File1), content of table class for all grade 2 classes, (File2) all students who are in grade 2 classes, (File3), partial result.

File4 is the structure of table student. File 5 is the structure of table class.

Note that class 31 in in grade 3, not grade 2.

What's wrong?
File1.jpg
File2.jpg
File3.jpg
File4.jpg
File5.jpg
0
Richard Korts
Asked:
Richard Korts
  • 6
  • 3
  • 2
1 Solution
 
DonKronosCommented:
SELECT DISTINCT a.uid, a.sid, a.fname, a.lname, b.class_code
from student a
inner join class b
on a.class = b.class_code and a.school_code = 'SES' and b.grade_code = '2'
order by a.class, a.lname, a.fname
0
 
Richard KortsAuthor Commented:
To DonKronos:

It still picks up students in class 31 (a 3rd grade class).

See attached file (File6)
File6.jpg
0
 
SharathData EngineerCommented:

Can you run this and post the result.
select distinct class_code  
  from class 
 where grade_code = '2' and class_code in (select class from student where school_code = 'SES')

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Richard KortsAuthor Commented:
To Sharath_123:

It produces the attached File7 (error).
File7.jpg
0
 
DonKronosCommented:
Hmmm  ...  doesn't seem logical.

Look in the class table and see what the grade_code is for class_code 31.  I have a suspicion you will find grade_code = 2 for class_code = 31.

If that is not the case then I have to figure out how SQL server is joining rows that do not meet the specified criteria?
0
 
Richard KortsAuthor Commented:
To all:

I did what DonKronos suggested (I had already confirmed that before I posted the original question). See attached (File 8).

I have the sinking feeling that it has to do with the fact that the name "class" is used as a column name in the table student and as a table name. I say this because I revised the original DonKronos query to eliminate the aliases & it falid saying something to the effect that "class" was not unique.

I hope I don't have to change the column name, it must be in 30 or 40 php scripts. Or the table name (even worse)
File8.jpg
0
 
SharathData EngineerCommented:

What i am trying to do is to know all the class_codes for school_code = 'SES' and grade_code = '2'
If the class code 31 has the grade_code = '2', then your query will return the records for that class code also.
select class_code  
  from class 
 where grade_code = '2' and class_code in (select class from student where school_code = 'SES')
 group by class_code
 
or
 
select count(class_code)
  from class 
 where grade_code = '2' and class_code in (21,22,31)

Open in new window

0
 
SharathData EngineerCommented:
pls run this
select class_code
  from class 
 where grade_code = '2' and class_code = 31

Open in new window

0
 
Richard KortsAuthor Commented:
To Sharath_123:

I ran this query:

SELECT * from class where school_code = 'SES' and grade_code = '2'.

It produces the arrached File9. This says to me that there are EXACTLY 2 rows in the table that meet this criteria and the row with class_code = 31 is NOT one of them.
File9.jpg
0
 
Richard KortsAuthor Commented:
To To Sharath_123:

I ran this query:

Select * from class where class_code = 31 and grade_code = '2". I got the attached. But note that the school_code is NOT 'SES'.

Ah, I think I see the solution. I have to further qualify b (class) that b.school_code = 'SES'. I'll try that.
File10.jpg
0
 
Richard KortsAuthor Commented:
The real solution is as I indicated in my last message, I had to add the FURTHER qualification "... and b.school_code = 'SES' ......"

You were most helpful & you led me in the right direction, so you get the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now