Comment Feed for Stored Procedure Help (TechOff on Channel 9)

Re: Stored Procedure Help

VBJB — Mon, 27 Mar 2006 13:49:36 GMT

I got it working, thank you guys!

Re: Stored Procedure Help

nwoolls — Mon, 27 Mar 2006 13:20:42 GMT

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.

Re: Stored Procedure Help

Yargnits — Mon, 27 Mar 2006 13:15:53 GMT

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)

Re: Stored Procedure Help

VBJB — Mon, 27 Mar 2006 12:14:42 GMT

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.

Re: Stored Procedure Help

nwoolls — Mon, 27 Mar 2006 12:00:31 GMT

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'
)

Re: Stored Procedure Help

W3bbo — Mon, 27 Mar 2006 11:53:39 GMT

RIGHT OUTER JOIN?