SQL Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

SQL Question

Author

Topic

kifeda
Posting Yak Master

136 Posts

Posted - 2005-02-23 : 20:00:37

I have a problem. I am trying to list the names of organizations and the numbers of jobs that they have posted to the site. The organizations are in a table called "communitypartners" and the jobs that they post are in a table called "jobs". The problem is my SQL Statement gives me the all the nonprofits that have jobs posted, but I want the nonprofits that don't have any jobs posted as well.

Here is my SQL statement:

SELECT COUNT(jobs.jobid) as TotalPostings, communitypartners.nameoforganization, communitypartners.city, communitypartners.nonprofitID, communitypartners.status
FROM communitypartners, jobs
WHERE communitypartners.status='Approved' and communitypartners.city like '%varcity%' and communitypartners.nameoforganization like '%nm%' and communitypartners.nonprofitid = jobs.nonprofitid

group by communitypartners.nameoforganization, communitypartners.city, communitypartners.nonprofitid, communitypartners.status
ORDER BY nameoforganization asc

I think there is something wrong with my group by clause.... Thanks in advance!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-23 : 21:25:17

I think you just need to left join to jobs: (I re-formatted just so I could read it better)


SELECT	COUNT(jobs.jobid) as TotalPostings
	,communitypartners.nameoforganization
	,communitypartners.city
	,communitypartners.nonprofitID
	,communitypartners.status
FROM	communitypartners
Left Outer JOIN	jobs
		ON communitypartners.nonprofitid = jobs.nonprofitid
WHERE	communitypartners.status='Approved' 
and	communitypartners.city like '%varcity%' 
and	communitypartners.nameoforganization like '%nm%' 
group by
	communitypartners.nameoforganization
	,communitypartners.city
	,communitypartners.nonprofitid
	,communitypartners.status
ORDER BY
	nameoforganization asc

Be One with the Optimizer
TG

kifeda
Posting Yak Master

136 Posts

Posted - 2005-02-23 : 22:03:26

Thank you so much! it works perfectly! Your the man!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources