Left Outer Join/Group By

Question

Post reply

Left Outer Join/Group By

slackbabbath

Valued Member

Points: 67
More actions
January 16, 2008 at 5:06 pm

#185742

Howdy all -
I'm using a left outer join and group by to return a results set with a column call ed "Apps_Per_Job". (I want each row to hold the number of applicants per job). It works, but a count of 1 is being returned in the results set for rows that do not exist in the right table.
Here is the SQL:
SELECT
j.Title,
j.JobID,
COUNT(*) AS Apps_Per_Job
FROM
Job j
LEFT OUTER JOIN
JobApplicant ja
ON
j.JobID = ja.JobID
GROUP BY
j.JobID,
j.Title
--HAVING COUNT(*) != NULL
END

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

slackbabbath Valued Member Points: 67 More actions · Answer 1

Nevermind, I found my mistake by searching content here.

Thanks

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Ummm.... wanna tell us what your mistake was, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

slackbabbath Valued Member Points: 67 More actions · Answer 3

Sure -

Although I haven't made time to learn exactly why it was wrong yet. I just found someone else doing the same type of query. I was in a hurry.

The bug happened when I used COUNT(*) . It works fine if I use COUNT(j.JobID)

I need to study up more on aggregate functions.