Need help with SELECT

[Anon]

I am trying to select from 3 tables. I need to select a "login" from a "users" table, according to an "id" that is stored in a table "ads".

I also need to select ratings from a table "ratings", according to the user id.

What I want to achieve is to be able to retrieve values from ads and users even if there is no rating for that particular user in the database, sort of like saying select this and that and IF you can, also select the ratings...

Can this be done?

Thanks for your help 🙂

Toby

$query = "
SELECT
ads.*,
COUNT ratings.id) AS deals,
COUNT(ratings.status) AS successes,
users.login
FROM
ratings,
ads,
users
WHERE
ratings.user_id = ads.user
AND
users.id = ads.user_id
AND
ads.date = 0
GROUP BY
ads.ad_id
ORDER BY
$order
LIMIT
$offset, $limit";

[simonsky]

Try to use JOIN.
Below SQL haven't tested, hope it helps.

$query = "
SELECT
ads.*,
COUNT ratings.id) AS deals,
COUNT(ratings.status) AS successes,
users.login
FROM
ads,
users
LEFT JOIN ratings
ON ratings.user_id = ads.user
WHERE
users.id = ads.user_id
AND
ads.date = 0
GROUP BY
ads.ad_id
ORDER BY
$order
LIMIT
$offset, $limit";

[Anon]

Thanks Simon. I just figured it out. Works great with a LEFT JOIN...

🙂

Thank you