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!!

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 ?

You'll have to post some sample data and output that demonstrate the problem, along with what you expect the output to be. Please edit your question and add this information. — Jim Garrison, Jul 26 '10 at 18:37
I don;t know if it wuill fix your problem, but you need a better understanding of left joins and where clauses. this one is SQL Server specific but the saame principle sould be applicable to all datbases: http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN — HLGEM, Jul 26 '10 at 18:39
You should edit your original post rather than post a response. In addition, we need to see some table schema. I'm not sure how the grouping fits into group by the company. — Thomas, Jul 27 '10 at 14:52

Thomas · Accepted Answer · 2010-07-27T02:57:44.467

2

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 )

edited Jul 27 '10 at 02:57

answered Jul 26 '10 at 19:36

Thomas

63,911
12
95
141

I was not aware that a `where` clause could change the meaning of a join. I thought the semantics was that the join behaved as if a virtual table was created, using the join criteria, and then the `where` clause was applied to that virtual table. Can you cite a reference for your description of the behavior? – Jim Garrison Jul 26 '10 at 21:25
@Jim Garrison - It is in the nature of the Join that it works that way. When you use a Left Join, you are asking for all items on the left and any matching items on the right. If you then apply a filter in the Where clause on a column from a table on the right, and do not account for Null, you are requesting that the value A. Exist, B. not be null and C. match the criteria (i.e. equal, not-equal, greater than etc. depending on the operator). Thus, the criteria `FCL.grouping <> 0` states that a row from FCL must exist, it must have a non-null grouping value and that value must not equal 0. – Thomas Jul 26 '10 at 22:17
@Jim Garrison - Continuing, if you apply a filter which requires that a row on the right side of a Left Join exists, you are in effect making an Inner Join. – Thomas Jul 26 '10 at 22:18
Can you point me to where this is described in a spec? My understanding is that the virtual table contains nulls for the data from missing rows (as opposed to an inner join, where there wouldn't be a virtual row at all). The `where` clause just see nulls, and shouldn't change the meaning of the join. It would be really helpful if you could find where in any SQL spec this is described. – Jim Garrison Jul 26 '10 at 23:13
@Jim Garrison - You are correct in that the Right table in a Left Join will contain NULLS for missing rows. **However**, you are then turning around and filtering out missing rows in the Where clause. The criteria `FCL.Grouping <> 0` is equivalent to writing `( FCL.Grouping IS NOT NULL And FCL.Grouping <> 0 )`. This is emergent behavior from the fact that a Left Join shows all rows from the left table and only matching rows from the right table. If you then filter out non-matching rows you have the equivalent logic to an Inner Join. – Thomas Jul 27 '10 at 14:33
@Jim Garrison - Here is a link to the SQL92 spec (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt). The spec will simply tell you that you should get NULL for a column from non-preserved table (i.e. the right side of a left join) where there is no matching row. That you know. Now, if you then tell the query to exclude NULLS, you are telling it to exclude non-matching rows. Said another way, by excluding NULLS, you are stating that you only want to see matching rows. – Thomas Jul 27 '10 at 14:45
@Jim Garrison - The following queries are equivalent: `Select * From A Inner Join B On A.PK = B.PK`, `Select * From A Left Join B On A.PK = B.PK Where B.PK Is Not Null`, `Select * From A Left Join B On A.PK = B.PK Where B.SomeOtherNotNullableColumn Is Not Null` – Thomas Jul 27 '10 at 14:46

MySQL joins with WHERE clause

1 Answers1