troubleshooting Question

Complicated SQL Query?

Avatar of ookjar
ookjar asked on
Databases
7 Comments1 Solution368 ViewsLast Modified:
Ok here's the question, sorry if it has already been answered I found one which nearly worked but my problem goes one stage further, so anyone with any ideas would be much appreciated...

I have the following table:-

itemkey  |registrationNumber | subjectID | subjectLevelsID
----------------------------------------------------------
   1     |       29          |    21     |      KS3
   2     |       29          |    22     |      KS2
   3     |       26          |    26     |      KS1
   4     |       21          |    18     |      KS1
   5     |       21          |    22     |      KS3
   6     |       21          |    21     |      KS2

etc..

I need a query to return all registrationNumbers whose (subject ID = 21 and subjectLevelsID = KS3 AND subjectID=22 AND subjectLevelsID = KS2) i.e. registrationNumber 29.

or multiple iterations thereof, i.e. subjectID and subjectLevelsID are AND'ed

N.B.

SELECT registrationNumber
FROM teacherskills
WHERE subjectID in (21,22) and subjectLevelsID in ("KS3")
group by registrationNumber
having count(*) = 2

This works for subjectID, but only for 1 subjectLevelsID if I put more in the in clause i.e. ("KS3","KS2") then it doesn't matter which subject corresponds, which is not what I want to achieve.

I hope this makes sense as to what I'm trying to achieve, i really do not want to change the table structure to have a finite number of subjectID's and Levels in the same row... rather defeats the object of a relational database!

b.t.w the database I'm using is mySQL, so joins are very limited!

Please help anyone!

Wayne
ASKER CERTIFIED SOLUTION
irinag

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros