how to union 3 table and group same id amount?

Question

how to solve 3 table and group same id?

   t1
----------
Id   a
1   100
1   600
2   800

  t2
----------
Id    b
1    600
2    700
3    400


  t3
----------
Id    c
2    400
3    800
4    100

i want result like this:

Id     a        b      c 
------------------------------
 1    700      600   
 2    800      700    400      
 3             400    800
 4                    100

Same id group by

That's not a union, that's known as a join. A union combines 2 different queries. — JNYRanger, Jun 27 '17 at 19:12
Which [DBMS](https://en.wikipedia.org/wiki/DBMS) are you using? Postgres? Oracle? — a_horse_with_no_name, Jun 28 '17 at 09:44
what is dbms sql???? Do you know know which dbms are you using? — Eric, Jun 29 '17 at 17:29

ScaisEdge · Accepted Answer · 2017-06-29T17:22:13.343

0

do the fact you have id in several table youn should get, eg: using union ,all the id you need for join

  select t.id, t1.a, t2.b, t3.c
  from ( 
       select id 
       from t1
       union
       select id
       from t2 
       union 
       select id 
       from t3 ) AS t 
    left join t1 on t.id = t1.id
    left join t2 on t.Id = t2.Id
    left join t3 on t.Id = t3.Id

and if you need sum for a,b,c

   select t.id, sum(t1.a), sum(t2.b), sum(t3.c)
      from ( 
           select id 
           from t1
           union
           select id
           from t2 
           union 
           select id 
           from t3 ) AS t 
        left join t1 on t.id = t1.id
        left join t2 on t.Id = t2.Id
        left join t3 on t.Id = t3.Id
    group by t.id

edited Jun 29 '17 at 17:22

answered Jun 27 '17 at 18:48

ScaisEdge

129,293
10
87
97

That would not return the row with `id = 4` from table `t3` – a_horse_with_no_name Jun 28 '17 at 09:43
i use ms access data base and 3 table use t1,t2,t3 like this i want get a querry in ms access self id group and sum - a colomn, sum -b colomn, sum -c colomn like this pls help solvethis – asu Jun 29 '17 at 16:38
You are using SQL or MS-ACCESS .. anyway answer updated adding as for tablename alias – ScaisEdge Jun 29 '17 at 17:23

Raunak Thomas · Answer 2 · 2017-06-27T19:00:04.003

0

To ensure that you are taking all possible values use full outer join. Though this will not work in mySQL. If that is the case then look at this answer

select coalesce(t1.id,t2.id,t3.id) as id, sum(t1.a) as a, sum(t2.b) as b,sum(t3.c) as c
from t1
outer join t2
   on t1.id = t2.id
outer join t3
   on t1.id = t3.id
      or t2.id = t3.id
group by id

edited Jun 27 '17 at 19:00

answered Jun 27 '17 at 18:54

Raunak Thomas

1,323
1
12
25

Roelof Briers · Answer 3 · 2017-06-27T19:06:12.520

0

Might be misunderstanding you, but looks like you just need to join the table more than doing a Union Operation on them. Below statement will only return records where all three tables have at least one record with the same id.

SQL would be:

SELECT TBL1.ID,
   TBL1.A,
   TBL2.B,
   TBL3.C
FROM A TBL1 (NOLOCK)
INNER JOIN B TBL2 (NOLOCK) ON TBL1.ID = TBL2.ID
INNER JOIN C TBL3 (NOLOCK) ON TBL1.ID = TBL3.ID

Two questions: 1. Which SQL engine to you use? 2. and do you need to return values where one table does not have the id?

edited Jun 27 '17 at 19:06

answered Jun 27 '17 at 18:57

Roelof Briers

105
9

The `nolock` makes no sense (and you need a full outer join anyway) – a_horse_with_no_name Jun 28 '17 at 09:43
use ms access data base and 3 table use t1,t2,t3 like this i want get a querry in ms access self id group and sum - a colomn, sum -b colomn, sum -c colomn like this pls help solvethis – asu Jun 29 '17 at 16:47

how to union 3 table and group same id amount?

3 Answers3