jeudi 30 avril 2015

select distinct sum of item that can exists in several tables

Let's say that i have 3 tables: Articles1,Articles2,Articles3.

It's possible that same articlegroup exists in two of theese tables. I only want to sum amount by each articlegroup existing in Articles1 and does not exists in the other tables.

Tables:

Articles1

| Id    |    ArticleName    |    Amount |
-----------------------------------------
  '1'         'Apple'              '2'
  '2'         'Orange'             '2'
  '3'         'Banana'             '3'

Articles2

| Id    |    ArticleName    |    Amount |
-----------------------------------------
  '1'         'Apple'              '2'
  '2'         'Orange'             '2'

Articles3

| Id    |    ArticleName    |    Amount |
-----------------------------------------
  '1'         'Apple'              '2'
  '2'         'Orange'             '2'

My code:

SELECT SUM(a1.Amount) 
FROM Articles1 a1
LEFT OUTER JOIN Articles2 a2
   ON a1.Id = a2.Id
LEFT OUTER JOIN Articles3 a3
   ON a1.Id = a3.Id
WHERE a1.Id <> a2.Id OR a1.Id <> a3.Id
GROUP BY a1.ArticleName

Fiddle

Aucun commentaire:

Enregistrer un commentaire