2 Replies - 2034 Views - Last Post: 26 February 2010 - 06:05 AM Rate Topic: -----

#1 fran101   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 91
  • Joined: 01-December 08

checking for multiple values in a sub query

Posted 24 February 2010 - 08:52 PM

I am trying to bring back results from multiple tables based on where they have not been seen before but to do this I need to be able to check for multiple values do not exist in a sub query.

I have been building up the query and have checked that the sub query brings back the values i need to check, and the outer query brings back the values i need along with the ones i need to check against the sub query but my problem lies in combining the two queries.

this is my attempt so far:
select  kslc.Weight, sa.LevelNo, sa.KeySkillNo, sac.CriteriaNo
                                from StudentAssessmentCriteria sac join StudentAssessment sa on sac.AssessmentNo = sa.AssessmentNo
									join KeySkills ks on sa.KeySkillNo = ks.KeySkillsNo
									join KeySkillLevel ksl on ks.KeySkillsNo = ksl.KeySkillNo
									join KeySkillLevelCriteria kslc on ksl.KeySkillNo = kslc.KeySkillNo and ksl.LevelNo = kslc.LevelNo
                                where sa.KeySkillNo = kslc.KeySkillNo and sa.LevelNo = kslc.LevelNo and sac.CriteriaNo = kslc.CriteriaNo                                 
                             and sa.AssessmentNo = 102
                                
                            and sa.LevelNo, sa.KeySkillNo, sac.CriteriaNo in 
                                                       
							(SELECT     StudentAssessment.KeySkillNo, StudentAssessment.LevelNo,  TakenAssessment.CriteriaNo
							 FROM       TakenAssessment INNER JOIN
										StudentAssessmentCriteria ON TakenAssessment.AssessmentNo = StudentAssessmentCriteria.AssessmentNo AND 
										TakenAssessment.CriteriaNo = StudentAssessmentCriteria.CriteriaNo INNER JOIN
										StudentAssessment ON StudentAssessmentCriteria.AssessmentNo = StudentAssessment.AssessmentNo
										WHERE     TakenAssessment.StudentNo = 2029 and TakenAssessment.Achieved = 1)


it is and sa.LevelNo, sa.KeySkillNo, sac.CriteriaNo in that i need to check in the subquery but not sure how to check multiple values.

Any help would be most appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: checking for multiple values in a sub query

#2 keakTheGEEK   User is offline

  • D.I.C Regular
  • member icon

Reputation: 108
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: checking for multiple values in a sub query

Posted 25 February 2010 - 01:41 PM

You can't test the values of those three fields against the subquery like that. You will have to break it up like so:

... and sa.LevelNo in (

Subquery
)
and sa.KeySkillNo in (
Subquery
)
and sac.Criteria in (
Subquery
)

You may want to look into making that subquery a common table expression so you don't have to keep writing it out each time...
Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24   User is online

  • D.I.C Lover
  • member icon


Reputation: 466
  • View blog
  • Posts: 2,212
  • Joined: 07-April 08

Re: checking for multiple values in a sub query

Posted 26 February 2010 - 06:05 AM

You can also look at using a Join instead of the inner query in the where clause like so:

select	kslc.Weight
		,sa.LevelNo
		,sa.KeySkillNo
		,sac.CriteriaNo 
from 
		StudentAssessmentCriteria sac 
join 
		StudentAssessment sa on sac.AssessmentNo = sa.AssessmentNo 
join 
		KeySkills ks on sa.KeySkillNo = ks.KeySkillsNo 
join 
		KeySkillLevel ksl on ks.KeySkillsNo = ksl.KeySkillNo 
join 
		KeySkillLevelCriteria kslc on ksl.KeySkillNo = kslc.KeySkillNo and ksl.LevelNo = kslc.LevelNo 
join
		(
			SELECT  StudentAssessment.KeySkillNo
					,StudentAssessment.LevelNo
					,TakenAssessment.CriteriaNo 
            FROM       
					TakenAssessment 
			INNER JOIN 
                    StudentAssessmentCriteria ON TakenAssessment.AssessmentNo = StudentAssessmentCriteria.AssessmentNo 
											  AND TakenAssessment.CriteriaNo = StudentAssessmentCriteria.CriteriaNo 
			INNER JOIN 
                    StudentAssessment ON StudentAssessmentCriteria.AssessmentNo = StudentAssessment.AssessmentNo 
            WHERE    
					TakenAssessment.StudentNo = 2029 and TakenAssessment.Achieved = 1
		)TakenAssessment on sa.LevelNo = TakenAssessment.LevelNo
						 AND sa.KeySkillNo = TakenAssessment.KeySkillNo
						 AND sac.CriteriaNo = TakenAssessment.CriteriaNo
where 
		sa.KeySkillNo = kslc.KeySkillNo 
		and sa.LevelNo = kslc.LevelNo 
		and sac.CriteriaNo = kslc.CriteriaNo                                  
        and sa.AssessmentNo = 102 



Also you mentioned

Quote

I need to be able to check for multiple values do not exist in a sub query.


So are you trying to return the Records from the outer query that do not exsist in the inner query? or return the records from the inner query that do not exsist in the outer query? Depending on that you could use either a Left or right join with a [column] is null in your where clause

This post has been edited by rgfirefly24: 26 February 2010 - 06:09 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1