Joined Query Help

[radar]

Okay I've got 2 tables I am pulling information from... one is users and the other is ratings...  The query works for the most part but doesnt work as well... 

here is my query setup
[code]
<?php
$data = $turbo->get_all("SELECT users.user_id AS user_id,
      users.username AS username,
                   users.class AS class,
      users.fname AS fname,
      users.lname AS lname,
      users.email AS email,
      users.reg_date AS reg_date,
      users.tot_cnt AS tot_cnt,
                                users.var_stats AS var_stats,
      count(ratings.status = 'complete') AS status,
      ratings.user_id as r_user_id
      FROM users LEFT JOIN ratings ON users.user_id=ratings.user_id
      GROUP BY users.user_id ORDER BY users.user_id ASC"); [/code]

Now this query works great for the most part...  only part that isnt working is the count(ratings.status = 'complete') AS status. 

I've got 6 users in the database -- and only 2 ratings complete in the database...

So right now if i echo out $users[status] on my screen it shows this..
400001

the first user has 4 ratings in the database but only 1 is complete, the next 4 have none in the database and the last has 1 in the database that is complete.

I have been trying to play with this quite a bit and cant figure it out...  can i get some help?

[shoz]

[code]
SELECT
users.user_id,
users.username,
users.class,
users.fname,
users.lname,
users.email,
users.reg_date,
users.tot_cnt,
users.var_stats,
count(ratings.user_id) AS status,
ratings.user_id AS r_user_id
FROM
users
LEFT JOIN
ratings
ON
users.user_id = ratings.user_id
AND
ratings.status = 'complete'
GROUP BY
users.user_id
ORDER BY
users.user_id ASC
[/code]

Note that you don't need to use an alias if the alias for the column will be the same as the current column name. Using "tablename.columname" syntax will still return "columname" as the column's name in the result.

[radar]

Sweet changed it over and it works perfectly... thank you so much..  that has been driving my crazy!

Also I was unaware that you didnt need the AS whatever in there -- I always thought that with joins you had to have them for the proper syntax.. but goes to show -- even a person who programs for a living doesnt know everything...