Problem with one of my LEFT JOIN and SUM the result of it

So I got a question about LEFT JOIN, this code returns different values for totalPoints depending on if the user got the group or not. (if user don't got group or event it returns the correct value)

I just want to grasp how to get the LEFT JOIN flow_has_vote ON flow_has_vote.flow_id=flows.id to work every time. I did a solution before with three query's, one that gets the group and event rule, one that checks if the user got the group or event considering the security and one to get the flow...

And I guess I could solve this by having two query's, one that gets the group and event rules and also check if the user got the group and event and then one that gets the flow depending on the user should have access to it.

Right now I'm getting every information needed in ONE query and then checking with IF statements if it should be printed or not...

So, my question is, is it possible to get the SUM(flow_has_vote.points) AS totalPoints to work this way? And do you know how?

And also I'm a bit curios, is one query the best way to work with this? Would it be justified to use two when you take into account performance?

 SELECT 
       flows.id AS flowId,
       flows.security,
       SUM(flow__has__vote.points) AS totalPoints,
       users.id AS userId,
       users.alias,
       flows.event_id AS eventId,
       events.group_id AS groupId,
       events.membershipRules AS eMR,
       groups.membershipRules AS gMR,
       user__has__group.permission AS userHasGroup,
       user__has__event.permission AS userHasEvent
    FROM   
       users,
       events   LEFT JOIN user__has__event ON user__has__event.user_id = '.$userId.',
       groups   LEFT JOIN user__has__group ON user__has__group.user_id = '.$userId.',
       flows    LEFT JOIN flow__has__vote ON flow__has__vote.flow_id=flows.id
    WHERE   
       flows.user_id = users.id AND 
       events.id = flows.event_id AND 
       groups.id = events.group_id AND 
       flows.id='.$flowId

And if you wonder what the SQL-statement is doing, getting the information for the flow(post), the information about the event and group that the flow is in, checking the user access to the group and event and also getting all the votes for the flow...

This is how the tables looks like...

FLOWS id,security,event_id,user_id
USERS id, alias
EVENTS id, name group_id, membershipRules
GROUPS id, name, membershipRules
USER__HAS__GROUP user_id,group_id,permission
USER__HAS__EVENT user_id,event_id,permission
FLOW__HAS__VOTE flow_id,user_id,points

This is the result I wish for...

+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
| flowId | security | totalPoints | userId | alias  | eventId | groupId | eMR | gMR | userHasGroup | userHasEvent | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
|      1 | 2        | 1337        | 5      | Pontus | 15      | 2       | 2   | 2   | 4            | 4            | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+

and one more example...

+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
| flowId | security | totalPoints | userId | alias  | eventId | groupId | eMR | gMR | userHasGroup | userHasEvent | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+
|      1 | 2        | 1337        | 6      | Kezia  | 15      | 2       | 2  开发者_运维知识库 | 2   | null         | null         | 
+--------+----------+-------------+--------+--------+---------+---------+-----+-----+--------------+--------------+

Enjoy your life ~ Pontus

---

So, basically the main point (IMHO) is not to include conditions on tables you LEFT JOINed in the WHERE clause, since this makes the LEFT JOIN behave like an INNER JOIN.

Start with trying this query (although I am sure you will have to make adjustments as I am not sure exactly what you want as a result, more about this later):

SELECT
       flows.id AS flowId,
       flows.security,
       SUM(flow__has__vote.points) AS totalPoints,
       users.id AS userId,
       users.alias,
       flows.event_id AS eventId,
       events.group_id AS groupId,
       events.membershipRules AS eMR,
       groups.membershipRules AS gMR,
       user__has__group.permission AS userHasGroup,
       user__has__event.permission AS userHasEvent
FROM users,
LEFT JOIN user__has__event 
     ON user__has__event.user_id = users.id,
LEFT JOIN events 
     ON user__has__event.event_id = events.id

LEFT JOIN user__has__group 
     ON user__has__group.user_id = users.id,
LEFT JOIN groups 
     ON user__has__group.group_id = groups.id 
     AND groups.id = events.group_id

LEFT JOIN flows 
     ON flows.user_id = users.id 
     AND events.id = flows.event_id 
     AND flows.id='.$flowId'
LEFT JOIN flow__has__vote 
     ON flow__has__vote.flow_id = flows.id

WHERE users.id = '.$userId.'
GROUP BY users.id

Here, I LEFT JOINed everything to the user, and also grouped by the user. I have a feeling you will want to add columns to the group by (flows.id?, events.id?)

Also, you may want to turn some of the LEFT JOINs to JOIN, so you will get only users who have a 'flow', for example.