Sql Query - count

I have the following SQL Query.  I want to return ONLY the records where the join only returns 1 record from the CompanySentResume table.

Select Company.CompanyID as CompanyID, Name,Email, SentResumeDate as "Resume Sent Date", FirstName From Company  
LEFT JOIN CompanySentResume
ON Company.CompanyID = CompanySentResume.CompanyID
 Where InvalidEmail = 0 AND DoNotContact ='0'
Order by SentResumeDate, CompanyID
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vbnetcoderAuthor Commented:
In order to get the count i can do this but not sure how to put it all together:

SELECT[CompanyID]
      ,Count([SentResumeDate])
  FROM [JobSearch].[dbo].[CompanySentResume]
  Group By CompanyID
  order by Count(SentResumedate) desc
0
Shaun KlineLead Software EngineerCommented:
You can add a HAVING clause to you GROUP BY statement:
SELECT[CompanyID]
       ,Count([SentResumeDate]) 
   FROM [JobSearch].[dbo].[CompanySentResume]
   Group By CompanyID 
   HAVING COUNT([SentResumeDate]) = 1
   order by Count(SentResumedate) desc 

Open in new window


You can then include this as a subquery to your original query.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to return ONLY the records where the join only returns 1 record from the CompanySentResume table.
Note the use of table aliases and indenting, and give this a whirl..

Select c.CompanyID as CompanyID, Name,Email, SentResumeDate as [Resume Sent Date], FirstName, Count(csr.CompanyID) as csr_count
FROM Company c
   LEFT JOIN CompanySentResume csr ON c.CompanyID = csr.CompanyID
Where InvalidEmail = 0 AND DoNotContact ='0'
GROUP BY c.CompanyID, Name, Email, SentResumeDate, FirstName
HAVING Count(csr.CompanyID) = 1
Order by SentResumeDate, CompanyID
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
not much point in the left join, if you require this:
HAVING Count(csr.CompanyID) = 1
0
vbnetcoderAuthor Commented:
Jim Horn,

I believe that is on the right track but I don't think it take into consideration that the send resume date could be different and in most cases WILL be different.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>the send resume date could be different and in most cases WILL be different.
Not really sure what you mean here.  It would be helpful if you can post a data mockup of both tables, and your expected output.

Experts here are not connected to your data source, and since there's not a data mockup to work with we are limited to posting air code based on a wompload of assumptions.
0
vbnetcoderAuthor Commented:
0
vbnetcoderAuthor Commented:
Here you go.  There can be on value in the Company table to many in the CompanySentResume table
0
PortletPaulfreelancerCommented:
This is a complete guess.
Perhaps you are looking for a way to only list "the most recent SentResumeDate"? The following query uses 2 analytic functions:
row_number() - by sorting in descending date order a value of 1 identifies "the most recent  SentResumeDate"
count() over(). - this is used to count the number of resume's sent, it's optional I think.
SELECT
      c.CompanyID
    , c.Name
    , c.Email
    , csr.SentResumeDate            AS [MOST RECENT Resume Sent Date]
    , c.FirstName
    , COALESCE(csr.resume_count, 0) AS resume_count
FROM Company c
      LEFT JOIN (
                  SELECT
                        CompanyID
                      , SentResumeDate
                      , ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY SentResumeDate DESC) AS rn
                      , COUNT(*) OVER (PARTITION BY CompanyID)                                  AS resume_count
                  FROM CompanySentResume
            ) csr
                  ON c.CompanyID = csr.CompanyID
                        AND rn = 1
WHERE c.InvalidEmail = 0
      AND c.DoNotContact = '0'
ORDER BY
      csr.SentResumeDate
    , c.CompanyID

Open in new window

If this guess isn't useful, would you please supply sample data and expected result.

The fastest way to resolve SQL related question is to supply sample data (data per table, suitable for inserts)
and the provide the "expected result"

This is a proven combination to get results.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
I was able to use this to get what i needed
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.