Avatar of Larry Brister
Larry Brister
Flag 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...
Microsoft SQL Server 2005Microsoft SQL ServerMicrosoft Development

Avatar of undefined
Last Comment
Larry Brister

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

(...where not exists ( select null from dbo.ContactEvents ce
                           where ce.ContactDate < Assignment.StartDate
                         )
)
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
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lowfatspread

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...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Guy Hengel [angelIII / a3]

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  
  ...
deighton

>>>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?
Larry Brister

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Larry Brister

ASKER
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]