CODEWITHSUNDEEP
mysqljoin
Paul
Paul

Reputation: 101

MYSQL: Return all rows in one table along with the sum of matching rows in another

EDIT - I apologize but I didn't include the correct information the first time!

I have the following two tables:

table 1
+----+-------+-------+
| id | model | color |
+----+-------+-------+
|  1 | 111AA | red   |
|  2 | 222BB | blue  |
|  3 | 333CC |       |
|  4 | 444DD | green |
+----+-------+-------+

table 2
+----+-------+-------+
| id | model | quant |
+----+-------+-------+
|  6 | 111AA |     2 |
|  7 | 222BB |     5 |
|  8 | 222BB |     3 |
+----+-------+-------+

I need a query that will take all the rows from table 1 where the color column is not empty along with the sum of the column quantity in table two that match a certain model (in the example given, model = '222BB') to produce the following table:

+----+-------+-------+------+
| id | model | color | quant|
+----+-------+-------+------+
|  1 | 111AA | red   |      |
|  2 | 222BB | blue  |    8 |
|  4 | 444DD | green |      |
+----+-------+-------+------+

This is what I tried so far:

SELECT t1.id, t1.model, t1.color, SUM(t2.quant) 
FROM table1 t1 LEFT OUTER JOIN table2 t2 
ON t1.id = t2.id 
WHERE t1.color != '' AND t2.model = '222BB'

However, this didn't work.

Any help is greatly appreciated.

Thanks!

Upvotes: 0

Views: 66

Answers (3)

Rinat
Rinat

Reputation: 429

To receive the expected table, run the following SQL query:

SELECT t1.id, t1.model, t1.color, IF(t2.model = '222BB', SUM(t2.quant), NULL) 
  FROM table1 t1 
   LEFT JOIN table2 t2 ON t1.model = t2.model 
  WHERE t1.color != '' 
  GROUP BY t1.model

The result will be the same as in your table. But I think it would be better to update the design to make join on ID column but not model-name.

Upvotes: 1

Jigar
Jigar

Reputation: 478

In Sql, you should not write != or == with null. It is highly suggested that you use IS NULL and IS NOT NULL clause.

http://www.tutorialspoint.com/sql/sql-null-values.htm

** select a.model,b.total from (select model from table1 where color is not null) a, (select model,sum(quant) total from table2 group by model) b where a.model=b.model; **

Upvotes: 0

user3818560
user3818560

Reputation:

Try this, 

select t1.id, t1.model,t1.color,sum(t2.quant)
from table1 t1
   left outer join table2 t2 on (t1.model = t2.model and t1.color <> ‘’)
group by t1.model

Upvotes: 0

Related Questions