SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast 1magic's Avatar
    Join Date
    Jan 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Help with JOIN needed

    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...
    Code:
    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
    Attached Images Attached Images

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should start joining them from agency in that case

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:
    Code:
    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

  4. #4
    SitePoint Enthusiast 1magic's Avatar
    Join Date
    Jan 2002
    Posts
    54
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow!

    That worked:
    Code:
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •