Problem with SQL Query

hawkseye · #1 (**permalink**) 02-18-06, 08:32

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"

hawkseye · #2 (**permalink**) 02-18-06, 08:46

im using mysql version 4.0.23

r937 · #3 (**permalink**) 02-18-06, 10:26

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

Internet Services

Web Development

Digital Marketing

Consumer Tech