MySQL joins with WHERE clause

Question

I have 2 queries which display 2 different values but the results from one query are unexpected.

Query1:

SELECT SUM(T.amount_reward_user) AS total_grouping
   FROM fem_user_cards UC
       LEFT JOIN fem_transactions T USING(card_number)
       LEFT JOIN fem_company_login FCL 
                        ON T.fem_company_login_id=FCL.fem_company_login_id
   WHERE UC.fem_user_login_id=193 
                      AND FCL.grouping<>0 AND T.from_group=1 AND T.authorised=1
   GROUP BY UC.fem_user_login_id

The above query gives me the total reward amount for the user 193. Here the condition is the reward amount which i am getting out of this query must be from the group which is evident from T.from_group=1. So this seems to be working correct.

Query2:

   SELECT SUM(T.amount_reward_user) AS total_grouping 
     FROM fem_transactions T 
LEFT JOIN fem_company_login FCL 
ON T.fem_company_login_id=FCL.fem_company_login_id 
    WHERE T.fem_user_login_id=193 
      AND FCL.grouping<>0 AND T.from_group=1 AND T.authorised=1

In this query even though T.from_group=1 it is adding up the reward amount from T.from_group=0 as well. Does anyone know what the problem is?

For example:

The user 193 purchased something in a company A(in the group so T.from_group=1) for $5 and got the reward of $1 and bought another product for $5 from company B(out of group so T.from_group=0) and get a reward of $1 again.

My expected output is: 1

I am getting:

Query1: 1

Query2: 2

I could simply use the query 1 but i have issues with other things when i use that so i need to understand what is going on really!!

Answer 1

The problem is that you have criteria in the Where clause against columns on the right side of the Left Join. In the both queries, this is And FCL.grouping <> 0. This effectively changes the Left Join to an Inner Join since you are in requiring that an FCL.grouping value exist and not be zero (or Null). What you want, I think, is to move that criteria into the On clause:

First query:

Select Sum(T.amount_reward_user) AS total_grouping
From fem_user_cards UC
    Left Join fem_transactions T Using(card_number)
    Left Join fem_company_login FCL
        On FCL.fem_company_login_id = T.fem_company_login_id
            And FCL.grouping<>0
Where UC.fem_user_login_id=193 
    And T.from_group=1 
    And T.authorised=1
Group By UC.fem_user_login_id

Second query

Select Sum(T.amount_reward_user) AS total_grouping
From fem_transactions T
    Left Join fem_company_login FCL
        On FCL.fem_company_login_id = T.fem_company_login_id
            And FCL.grouping<>0
Where UC.fem_user_login_id=193 
    And T.from_group=1 
    And T.authorised=1

What is not clear in all of this is what you are trying to achieve. I.e., why even have the Left Joins in either query if you are not filtering on them. If you do specifically want to filter for rows where there is a fem_company_login value with a grouping value <> 0, then use your original query and change the Left Join to an Inner Join. If you want to filter for people that either have a grouping value <> 0 or no value at all then do something like:

Select Sum(T.amount_reward_user) AS total_grouping
From fem_transactions T
    Left Join fem_company_login FCL
        On FCL.fem_company_login_id = T.fem_company_login_id
Where UC.fem_user_login_id=193 
    And T.from_group=1 
    And T.authorised=1
    And ( FCL.PrimaryKeyColumn Is Null Or FCL.grouping <> 0 )

Answer 2

My aim is to get the sum of reward amounts from the database. The sum should be of different grouped companies. That is if we have 5 different grouped companies and user is using his moneycard in those 5 companies, I should get 5 different totals. If he used his card in any company which is not in the group the total should not include transaction from this un-grouped company. I have C++ code to feed the database as transactions happens. My fem_transactions table has company_id, amount_reward_user, fem_user_login_id, from_group (this decides if the user has transacted out of the group or from the group), authorized etc..

I want to filter the amount spent by the user 193 in each group separately.

I understood the JOINTS concepts now but i need to know why t.from_group on the second query is ignored ?