Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

SQL Server not exists in joined table

I have a long select table with quite a few joins

One of the joins I need to add is to have everything from assignments

AND

JOIN to ContactEvents  on AssignmentID  = Assignment ID
and get everything FROM contact events where Assignment.StartDate < ContactEvent.ContactDate

Blah...blah...blah...
LEFT JOIN      dbo.ContactEvents
          ON   Assignment.[Assignment Number] = dbo.ContactEvents.[Assignment Number]

(...where not exists dbo.ContactEvents
.ContactDate < Assignment.StartDate)

Blah...blah...blah...
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

(...where not exists ( select null from dbo.ContactEvents ce
                           where ce.ContactDate < Assignment.StartDate
                         )
)
Avatar of Larry Brister

ASKER

angelIII
I'm not always going to have data at ALL in the contact table.  So a valid "return" might be null in my join.

Or am I missing something here?

Following is my actual select statement

The ClientContactEvents is the table in question. I only need to exclude it from my return if a date exists that is LESS THAN the Assignment.[Start Date]

(I know...a bunch of the columns have spaces...in the middle of normalizing as well. On a new job.)

SELECT  dbo.Assignment.[Client ID],
            dbo.Assignment.[Job Order ID],
            dbo.Assignment.[Assignment Number],
            dbo.Assignment.[Lead ID],
            ISNULL(dbo.Assignment.[Employee Last Name], [dbo].[leads].[Last_name]) AS [Employee Last Name],
            ISNULL(dbo.Assignment.[Employee First Name], [dbo].[leads].[First_name]) AS [Employee First Name],
            dbo.leads.assignment_phone,
            dbo.leads.email,
            dbo.Assignment.[Start Date],
            dbo.Assignment.[STE End Date],
            dbo.Assignment.Status,
            dbo.Assignment.[Account Manager],
            dbo.Assignment.[Staffing Manager],
            dbo.Assignment.[Bill Rate],
            dbo.[Job Order].Shift,
            dbo.[Assignment].Specialty,
            dbo.[Job Order].[Account Executive],
            'Check',
            dbo.ClientContactEvents.[Contact Date]
FROM      dbo.Assignment WITH (NOLOCK)
                  LEFT JOIN      [dbo].[leads] WITH (NOLOCK)
                        ON            [dbo].[leads].[lead_id] = [dbo].[Assignment].[Lead ID]
                  INNER JOIN      dbo.[Job Order] WITH (NOLOCK)
                        ON            dbo.[Job Order].[Job Order ID] = dbo.Assignment.[Job Order ID]
                  INNER JOIN      dbo.Specialty WITH (NOLOCK)
                        ON            dbo.Specialty.Specialty = dbo.[Job Order].Specialty
                  LEFT JOIN      dbo.ClientContactEvents
                        ON            Assignment.[Assignment Number] = dbo.ClientContactEvents.[Assignment Number]                        
WHERE      dbo.Assignment.Status = 'Pending New'
            AND
            Assignment.[Start Date] <=GETDATE()
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
sorry don't follow that ...

you need to  give us a proper statement of the requirements ...

so that we can understand the need for the exists / left joins etc...

it would be better to post your current query...
btw, you should start using table aliases:

so, instead of this:

 .... dbo.ClientContactEvents.[Contact Date]
FROM      dbo.Assignment WITH (NOLOCK)
                  LEFT JOIN      [dbo].[leads] WITH (NOLOCK)
                        ON            [dbo].[leads].[lead_id] = [dbo].[Assignment].[Lead ID]
 ...
    JOIN dbo.ClientContactEvents  
   ON
 ...

you would do:

 ... cce.[Contact Date]
FROM      dbo.Assignment WITH (NOLOCK) a
                  LEFT JOIN      [dbo].[leads] WITH (NOLOCK) l
                        ON  l.[lead_id] = a.[Lead ID]
  ...
JOIN  dbo.ClientContactEvents cee
   ON  
  ...
>>>The ClientContactEvents is the table in question. I only need to exclude it from my return if a date exists that is LESS THAN the Assignment.[Start Date]

do you mean any date in the ClientContactEvents  table, or just in the particular row, or for a particular group of rows?
angelll
I agree on the aliases.

believe it or not...
I'm just the Sr .Net developer on my new job...
The SQL dba INSISTS on full path programming. Wants to see the entire table name etc.
Can't budge him.
Can't budge him on the spaces in the column names either

Darnd'st thing I ever saw in a dba.

By the way...you put me on the right track.  Points being awarded momentarily
Put me on the right track.

This seems to get the job done

SELECT  dbo.Assignment.[Client ID],
            dbo.Assignment.[Job Order ID],
            dbo.Assignment.[Assignment Number],
            dbo.Assignment.[Lead ID],
            ISNULL(dbo.Assignment.[Employee Last Name], [dbo].[leads].[Last_name]) AS [Employee Last Name],
            ISNULL(dbo.Assignment.[Employee First Name], [dbo].[leads].[First_name]) AS [Employee First Name],
            dbo.leads.assignment_phone,
            dbo.leads.email,
            dbo.Assignment.[Start Date],
            dbo.ClientContactEvents_DEVLB.[Contact Date],
            dbo.Assignment.[STE End Date],
            dbo.Assignment.Status,
            dbo.Assignment.[Account Manager],
            dbo.Assignment.[Staffing Manager],
            dbo.Assignment.[Bill Rate],
            dbo.[Job Order].Shift,
            dbo.[Assignment].Specialty,
            dbo.[Job Order].[Account Executive],
            'Check' [Check]
FROM      dbo.Assignment WITH (NOLOCK)
                  LEFT JOIN      [dbo].[leads] WITH (NOLOCK)
                        ON            [dbo].[leads].[lead_id] = [dbo].[Assignment].[Lead ID]
                  INNER JOIN      dbo.[Job Order] WITH (NOLOCK)
                        ON            dbo.[Job Order].[Job Order ID] = dbo.Assignment.[Job Order ID]
                  INNER JOIN      dbo.Specialty WITH (NOLOCK)
                        ON            dbo.Specialty.Specialty = dbo.[Job Order].Specialty
                  LEFT JOIN      dbo.ClientContactEvents_DEVLB
                        ON            Assignment.[Assignment Number] = dbo.ClientContactEvents_DEVLB.[Assignment Number]
WHERE      dbo.Assignment.Status = 'Pending New'
                        AND
                  Assignment.[Start Date] <=GETDATE()
                        AND
                  ISNULL(dbo.ClientContactEvents_DEVLB.[Contact Date],GETDATE()) >= Assignment.[Start Date]