Stored Procedure Help

Posted By: VBJB | Mar 27th, 2006 @ 4:42 AM
page 1 of 1
Comments: 6 | Views: 3146
#Mar 27th, 2006 @ 4:42 AM
VBJB
VBJB
I need help with a SQL stored procedure.

I have two tables one called Job and the other called JobTracker_Job. I need a Select query that says SELECT * FROM Job but not where there is an entry in Job and JobTracker_Job that match WHERE status = 'Active'.

My only thought is some sort of JOIN that says select all from JOB where there is no matching rows in JobTracker_Job and Job. Does this make sense?
#Mar 27th, 2006 @ 5:00 AM
nwoolls
nwoolls
Select *
From Job j1
Where Not Exists(
  Select j2.JobId
  From Job j2
  Where j2.JobId = j1.JobId
    And Exists (
      Select jt.JobTrackerId
      From JobTracker jt
      Where jt.JobId = j1.JobId
    )
    And j2.Status = 'Active'
)
#Mar 27th, 2006 @ 5:14 AM
VBJB
VBJB
W3bbo wrote:
RIGHT OUTER JOIN?


I do not think RIGHT OUTER JOIN will work. JobTracker is a subset of Job. I want all the entries from Job, but I do not want the entries that are in common.

I am going to try nwoolls submission.
#Mar 27th, 2006 @ 6:15 AM
Yargnits
Yargnits
I'm not sure what the performance of this would be versus what was posted by nwoolls but:

Select *
From Job
Where status = 'Active'
     And JobId Not In (Select JobId From JobTracker_Job)
#Mar 27th, 2006 @ 6:20 AM
nwoolls
nwoolls
Yargnits wrote:
I'm not sure what the performance of this would be versus what was posted by nwoolls but


Worth testing, but I think "In" generates an "Exists" under the hood, so they should be similar.
#Mar 27th, 2006 @ 6:49 AM
VBJB
VBJB
I got it working, thank you guys!
page 1 of 1
Comments: 6 | Views: 3146