• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Reworking a NOT IN query

Can you advise on how to rework this section? using a JOIN or the best method for tuning? thanks!


AND 
 ( 
TrainingCourse.TrainingCourseId IN (Select TrainingCourseId from TrainingCourseUnit WHERE UnitId IN (Select UnitId from EmployeeUnitJobTypeUnitView where EmployeeId = Employee.EmployeeId))
 OR 
TrainingCourse.TrainingCourseId NOT IN (Select TrainingCourseId from TrainingCourseUnit)
 ) 

Open in new window

0
Starr Duskk
Asked:
Starr Duskk
  • 4
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
To best analyze/correct performance, would need to see the definition of "EmployeeUnitJobTypeUnitView" and the current query plan for this code snippet.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Can you just give me a generalization on if I'm using a

where NOT IN (select....

you suggest something better....

like instead of using a

where IN (select

I'd use a join clause.

thanks.
0
 
SharathData EngineerCommented:
Provide the complete query.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)
0
 
SharathData EngineerCommented:
I don't think you provided the complete query. There is Employee in the WHERE condition. Without knowing the full query, its tough to reconstruct.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Okay, PLEASE FORGET my first example.

All I want to know is another option for a NOT IN.

Can anyone please give me some examples based on this simple little query?

select * from table where otherfieldID NOT IN (select otherfieldID from OtherTable)

thanks.
0
 
SharathData EngineerCommented:
SELECT T1.*
FROM Table1 T1
LEFT JOIN Table2 T2
ON T1.JoinColumn = T2.JoinColumn
WHERE T2.JoinColumn IS NULL
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Excellent! Thanks!
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

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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