Help with JOIN needed

[1magic]

Hello,

I have a complicated SQL expression problem (at least for me). I will really appreciate if someone take me out of my problem.

Now to the point:

My appliction is about travel agencies, posting offers in some categories. I have 3 tables:

jour_offers
jour_agency
jour_categories

I have `jour_offers`.agencyID related to `jour_agency`.ID
and `jour_offers`.categoryID related to `jour_categories`.ID

My agency wants to see all the `jour_categories` and how many offers it has in every one of them.

This is what I did...

SELECT jour_categories.ID,
       jour_categories.name,
       COUNT( jour_offers.ID ) AS COUNT_OF_OFFERS
FROM jour_categories
   LEFT JOIN jour_offers ON (jour_categories.ID = jour_offers.categoryID)
GROUP BY jour_categories.ID

...and I get a list of all categories with counting all posted offers. But when I try to count only offers posted by the specific agency (WHERE jour_offers.agencyID = 2) it returns only categories where there are 1 or more offers.

If anybody can help me... it will be great! Thanks in advance!

See the databases in the attached .JPG

[sylow]

You should start joining them from agency in that case

[shane]

Try this:

SELECT	jour_categories.ID,
	jour_categories.name,
	COUNT( jour_offers.ID ) AS COUNT_OF_OFFERS
FROM	jour_categories
	LEFT JOIN jour_offers ON (jour_categories.ID = jour_offers.categoryID)
	AND jour_offers.agencyID = 2
GROUP BY jour_categories.ID

[1magic]

wow!

That worked:

SELECT	jour_categories.ID,
	jour_categories.name,
	COUNT( jour_offers.ID ) AS COUNT_OF_OFFERS
FROM	jour_categories
	LEFT JOIN jour_offers ON (jour_categories.ID = jour_offers.categoryID)
	AND jour_offers.agencyID = 2
GROUP BY jour_categories.ID

It is very elegant, something like using a WHERE without really using it, or maybe...

Anyway, thanks a lot Shane!