SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Threaded 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


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
  •