Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    6

    Red face Unanswered: Problem with SQL Query

    Below is my query with which im having some problems. What i want is that select all customers from customerInfo table who have more than 0 ads in adIno table and count how many ads they have.
    Now the problem is that my query select all records even those customers who have 0 ads. Please help me

    "SELECT c.customerID, c.storeName, c.Address, c.State, c.City, count(a.adID) as noOfAds
    FROM customerInfo c left join adInfo a ON c.customerID = a.customerID
    WHERE c.`storeName` IS NOT NULL GROUP BY c.customerID ORDER BY c.storeName ASC"

  2. #2
    Join Date
    Dec 2004
    Posts
    6
    im using mysql version 4.0.23

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. change LEFT OUTER to INNER
    2. fix your incorrect GROUP BY
    Code:
    SELECT c.customerID
         , c.storeName
         , c.Address
         , c.State
         , c.City
         , count(a.adID) as noOfAds 
      FROM customerInfo c 
    inner
      join adInfo a 
        ON c.customerID = a.customerID 
     WHERE c.`storeName` IS NOT NULL 
    GROUP 
        BY c.customerID
         , c.storeName
         , c.Address
         , c.State
         , c.City
    ORDER 
        BY c.storeName ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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