-1

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

asu
  • 19
  • 5

3 Answers3

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
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • That would not return the row with `id = 4` from table `t3` –  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
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
Raunak Thomas
  • 1,393
  • 1
  • 12
  • 28
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?

Roelof Briers
  • 115
  • 10
  • The `nolock` makes no sense (and you need a full outer join anyway) –  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