We help IT Professionals succeed at work.

Sql Query - count

614 Views
Last Modified: 2014-08-08
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
Comment
Watch Question

Author

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
Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
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.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

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

Author

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.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>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.

Author

Commented:
Here you go.  There can be on value in the Company table to many in the CompanySentResume table
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I was able to use this to get what i needed

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.