Re: Query with no-correspondence results

Sorry for digging this up but I have a more complex funcionality to
implement and I was hoping you could help

I had

mydatabase_votecontext
(id, name)
(1, Flavour)
(2, Smell)
(3, Usability)
(4, Size)

mydatabase_vote
(id, thing, context, user, vote)
(1, Potatoes, Flavour, Me, 2.0)
(2, Potatoes, Smell, Me, 4.3)
(3, Potatoes, Usability, Me, 4.0)
(4, Carrots, Flavor, Me, 3.0)
(5, Cars, Smell, Me, 4.2)
(6, Cars, Usability, Me, 4.9)

and now I also have:

mydatabase_user
(id, ......)

mydatabase_facebookuser)
(id, user_id, gender, ...)

I would like to query the average votes by gender :O

I thought if the original was

SELECT mb.id, mb.name, COALESCE( AVG( vote ) , 0.0 ) AS average_vote
FROM mydatabase_votecontext AS md
LEFT JOIN restudante_vote ON ( mydatabase_vote.context_id = md.id
AND thing_id = 1)
GROUP BY thing_id, name
ORDER BY md.id

then

SELECT md.id, md.name, COALESCE( AVG( vote ) , 0.0 ) AS average_vote
FROM mydatabase_votecontext AS md
LEFT JOIN restudante_vote ON ( mydatabase_vote.context_id = vc.id
AND thing_id = 1),
facebook_facebookprofile
WHERE mydatabase_vote.user_id = facebook_facebookprofile.user_id
AND facebook_facebookprofile.gender = 'male'
GROUP BY thing_id, name
ORDER BY md.id

could be the solution. But the "filter" by gender comes after the LEFT
JOIN and is messing everything up. It will cut off a vote_context if
no vote for that context exists (the original, but solved, problem)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to django-users+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.