Re: Voting SQL

[Jassim Rahma]

This is my update SQL

SELECT nuwab.nuwab_id, nuwab.nuwab_uuid, nuwab.nuwab_name,
nuwab.governorate, nuwab.area, IFNULL(nuwab_votes.vote_rating, 0) AS rating
FROM nuwab
LEFT JOIN nuwab_votes ON nuwab_votes.nuwab_id = nuwab.nuwab_id
LEFT JOIN oneid.users ON oneid.users.user_id = nuwab_votes.user_id
GROUP BY nuwab.nuwab_id
ORDER BY nuwab.nuwab_name;


The problem is that the above will just JOIN the tables regardless of the user and what I want to to show the data from nuwab_votes only for user_id = param_user, however if I do this:

SELECT nuwab.nuwab_id, nuwab.nuwab_uuid, nuwab.nuwab_name,
nuwab.governorate, nuwab.area, IFNULL(nuwab_votes.vote_rating, 0) AS rating
FROM nuwab
LEFT JOIN nuwab_votes ON nuwab_votes.nuwab_id = nuwab.nuwab_id
LEFT JOIN oneid.users ON oneid.users.user_id = nuwab_votes.user_id
WHERE nuwab_votes.user_id = param_user
GROUP BY nuwab.nuwab_id
ORDER BY nuwab.nuwab_name;

it will only list the rows inside the nuwab_votes, for example my nuwab is 40 rows and if the user rated 21 then it will only show the 21 where there are other 19 which he did not rate for them but I still want to show then but with a null rating here:

IFNULL(nuwab_votes.vote_rating, 0) AS rating