CODEWITHSUNDEEP
mysqlwhere-clauseouter-join
GGSoft
GGSoft

Reputation: 429

RIGHT OUTER JOIN doesn't work with WHERE clause

Suppose I have two tables :

     CREATE TABLE `test_a` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(20) DEFAULT NULL,
     PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And 

CREATE TABLE `test_b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `A_id` int(11) NOT NULL,
  `Amount` float NOT NULL,
  `cat` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `test_b_ibfk_1` (`A_id`),
  CONSTRAINT `test_b_ibfk_1` FOREIGN KEY (`A_id`) REFERENCES `test_a` (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

In test_a I entered data

1, A
2, B
3, C

In second table I entered following data: 1, 1, 4.78, 1
2, 2, 77, 1
3, 1, 0.22, 1
4, 2, 13, 1

The task is: to select sum of all amounts, grouped by a_id, all data from test_a must be appear. Thus, I’m using right outer join. When I ran following query: 

SELECT a.name, sum(b.amount)
  FROM test_b AS b RIGHT OUTER JOIN test_a AS a ON b.A_id = a.id
 GROUP BY b.A_id;

That gives a result set as desired. Like this: C -- null
A -- 5
B -- 90

But when I used WHERE clause:

          SELECT a.name, sum(b.amount)
          FROM test_b AS b RIGHT OUTER JOIN test_a AS a ON b.A_id = a.id
          **where b.cat =1**
           GROUP BY b.A_id;

I get the result: A -- 5
B -- 90

My question is: How to achieve same result with WHERE clause. (I want ‘C – null’, to be appear)

Thank in advance!!!

Upvotes: 0

Views: 123

Answers (1)

VMai
VMai

Reputation: 10336

You've got to include your condition of the joined table b in the ON condition:

SELECT a.name, sum(b.amount) 
FROM 
    test_b AS b 
RIGHT OUTER JOIN 
    test_a AS a 
ON 
    b.A_id = a.id AND b.cat =1 
GROUP BY b.A_id;

Because if used in the WHERE clause it changes the OUTER JOIN implicitly to an INNER JOIN.

Upvotes: 1

Related Questions