Topic: joined table counting and all of first tabel return records sql help

[cristalmolix]

hi guys

im joing 2 tables onto a users and then joining another table onto them tables and then i want all the users and then a count of all there observations and a count of all the uploaded images for that observation.

Code: [Select]

SELECT
u.username AS Username,
u.id AS 'User ID',
u.title AS 'Title',
u.forename AS 'First Name',
u.surname AS 'Last Name',
u.address1 AS 'Address Line 1',
u.address2 AS 'Address Line 2',
u.town AS 'Town/City',
c.title AS 'County',
u.postcode AS 'Postcode',
u.age AS 'Age',
u.youth_name AS 'School Name',
count(i.observation_id) AS 'Number Of Uploads',
count(o.id) AS 'Number Of Identifications',
CASE WHEN u.ispot = 1
THEN 'No'
ELSE 'Yes' END AS 'Mailing List Subscribed (Yes\No)',
u.email AS 'Email Address'
FROM user u
LEFT JOIN (observation o, county c, image i)
ON (u.id = o.user_id AND u.county_id = c.id AND i.observation_id = o.id)
WHERE
1=1
{$from_to_filter}
{$subscribed_filter}
GROUP BY o.user_id

its just not working out in order to count i have to use group by and that makes the 1300 users turn into 230 for some reason ???????????


any help suggestions would be very much appreciated

thanks

[fenway]

Sorry, I don't follow.

[cristalmolix]

thanks fen, i ended up doing it in arrays, the sql is too much