Problem in Selecting Data

[callraheel]

Hello User
This is a length question but i will try to tell you as much as briefly as possible and informative and understandable as well.

Im doing the following query

SELECT CombID,CombName,
@subject1:=(SELECT Subject FROM subjects WHERE subjects.SubjectID=combinations.Subject1ID) AS Subject1,
@subject2:=(SELECT Subject FROM subjects WHERE subjects.SubjectID=combinations.Subject2ID) AS Subject2,
@subject3:=(SELECT Subject FROM subjects WHERE subjects.SubjectID=combinations.Subject3ID) AS Subject3,
@myclass:=(SELECT Class FROM classes WHERE classes.ClassID=1) AS MyClass
FROM combinations,classes WHERE classes.ClassID=1;

subjects table has following data

+-----------+--------------------+---------+-----------+
| SubjectID | Subject | AddedBy | FromHost |
+-----------+--------------------+---------+-----------+
| 1 | Humanities | root | localhost |
| 2 | Computer | root | localhost |
| 3 | Chemistry | root | localhost |
| 4 | Biology | root | localhost |
| 5 | Statistics | root | localhost |
| 6 | Physics | root | localhost |
| 7 | Pak Studies | root | localhost |
| 8 | Philosophy | root | localhost |
| 9 | Economics | root | localhost |
| 10 | Mathematics | root | localhost |
| 11 | English | root | localhost |
| 12 | Urdu | root | localhost |
| 13 | Geography | root | localhost |
| 14 | Drawing | root | localhost |
| 15 | Islamiat | root | localhost |
| 16 | Physical Education | root | localhost |
+-----------+--------------------+---------+-----------+

Classes table has following data
+---------+----------+---------+-----------+
| ClassID | Class | AddedBy | FromHost |
+---------+----------+---------+-----------+
| 1 | 1st year | root | localhost |
| 2 | 2nd year | root | localhost |
+---------+----------+---------+-----------+

And combinations table has following data...
+--------+-----------------+------------+------------+------------+---------+---------+-----------+
| CombID | CombName | Subject1ID | Subject2ID | Subject3ID | ClassID | AddedBy | FromHost |
+--------+-----------------+------------+------------+------------+---------+---------+-----------+
| 1 | Pre-Engineering | 10 | 6 | 3 | 1 | root | localhost |
| 2 | Pre-Medical | 1 | 9 | 3 | 1 | root | localhost |
| 3 | General Science | 1 | 2 | 3 | 1 | root | localhost |
| 4 | Humanities | 1 | 2 | 3 | 1 | root | localhost |
| 5 | Pre-Engineering | 1 | 2 | 3 | 2 | root | localhost |
| 6 | Pre-Medical | 1 | 2 | 3 | 2 | root | localhost |
| 7 | General Science | 1 | 2 | 3 | 2 | root | localhost |
| 8 | Humanities | 8 | 2 | 3 | 2 | root | localhost |
+--------+-----------------+------------+------------+------------+---------+---------+-----------+


I have written all data so that you can judge why correct result are not coming..
Now what i got when i do the above stated query...
+--------+-----------------+-------------+-----------+-----------+----------+
| CombID | CombName | Subject1 | Subject2 | Subject3 | MyClass |
+--------+-----------------+-------------+-----------+-----------+----------+
| 1 | Pre-Engineering | Mathematics | Physics | Chemistry | 1st year |
| 2 | Pre-Medical | Humanities | Economics | Chemistry | 1st year |
| 3 | General Science | Humanities | Computer | Chemistry | 1st year |
| 4 | Humanities | Humanities | Computer | Chemistry | 1st year |
| 5 | Pre-Engineering | Humanities | Computer | Chemistry | 1st year |
| 6 | Pre-Medical | Humanities | Computer | Chemistry | 1st year |
| 7 | General Science | Humanities | Computer | Chemistry | 1st year |
| 8 | Humanities | Philosophy | Computer | Chemistry | 1st year |
+--------+-----------------+-------------+-----------+-----------+----------+
But you can see that there are only 4 combinations for 1st year (ClassID=1) but it is displaying 8 rows

So anyone can help me out..
im really tired of it!
Regards
Raheel

 

[ayozzhero]

It is pretty lengthy... and is not a part of the .Net programming actually. It is how you write the SQL query. I cannot figure out what you want to do, but the way the query is done is a bit confusing. Why don't you try 'JOIN' or/and its family instead.

Sometimes, multi-level query can be simplied using temporary table. Save related result in a temporary table and begin another query from there. It avoids a lot of confusion.

 

[callraheel]

You are right but you know the temporary table usage in ok beyond v4.1 of MySQL. So here we can use the variable to do the task, instead of creating and then dropng the table. And now i will look into Joins also but for now if anyone knows whats the problem then write back and what you did not understand in this query please write so that i can explain...

Regards

 

[callraheel]

Problem Solved

Well my problem is solved
in the last where clause i added " combinations.ClassID=classes.ClassID" and it produced my desired result..
Thanks for replying dude...
Cheers!