Mysql query using inner join

Question

I have the following table t

id   a   b    c
1    1   1    1
2    2   1    1

and the table t1

id  a   b   c  p
1  1    1   1   47 
2  1    1   1   2
3  2    1   1   78

My purpose is to get a table

    id   a   b    c  p 
    1    1   1    1  49
    2    2   1    1  78

I tried the following script but it not works @y=@y+t1.p is always null :

select t.id,t1.a, t1.b , t1.c ,@y=@y+t1.p from t1
inner join t
where t.a=t1.a
and t.b=t1.b
and t.c=t1.c

For more details enter link description here

Filipe Silva · Accepted Answer · 2013-12-07 19:00:32Z

2

This gives the result you want:

select t.id,
       t1.a, 
       t1.b , 
       t1.c ,
       SUM(t1.p) from t1
inner join t ON t.a=t1.a and t.b=t1.b and t.c=t1.c
group by t.id, t1.a, t1.b, t1.c

sqlfiddle demo

This uses SUM and GROUP BY. Make sure you use all the columns from the select in the GROUP BY to avoid undesired results.

answered Dec 7, 2013 at 19:00

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mosty Mostacho Over a year ago

That's right: the group by has been properly used. However, there could be a small improvement in terms of performance. Assuming the ONLY_FULL_GROUP_BY sql mode is not set (which is the default) and assuming t.id functionally identifies all the the other fields in the t table then the group by could only be performed by t.id

Community · Accepted Answer · 2017-05-23 11:57:32Z

1

Try using sum() function over group by and colease. Plenty of examples in google.

One link is here

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Dec 7, 2013 at 18:54

Ashis Kumar Sahoo

1449 bronze badges

Comments

Raymond Nijland · Accepted Answer · 2013-12-07 18:54:17Z

1

The calculation part needs to be done this way (if you need it sometime again)

SET @y = 0;

select t.id,t1.a, t1.b , t1.c ,(@y:=@y + t1.p) from t1
inner join t
where t.a=t1.a
and t.b=t1.b
and t.c=t1.c
;

see demo http://www.sqlfiddle.com/#!2/68a61/8

answered Dec 7, 2013 at 18:54

Raymond Nijland

11.6k2 gold badges26 silver badges37 bronze badges

Comments

Bojan Dević · Accepted Answer · 2013-12-07 18:59:57Z

1

You need to add the GROUP BY statement and use SUM

SELECT
  t.id,
  t1.a, 
  t1.b, 
  t1.c,
  SUM(t1.p) AS p
FROM 
  t1
  INNER JOIN t
     USING (a, b, c)
GROUP BY a, b, c

You can see it running here. It is possible to do it with variables but type of problem is usually solved with aggregate functions.

edited Dec 7, 2013 at 18:59

answered Dec 7, 2013 at 18:52

Bojan Dević

1,87514 silver badges25 bronze badges

2 Comments

Raymond Nijland Over a year ago

Note t.id is be random value now (your lucky its correct now).. I think you should read this dbasquare.com/2012/05/15/… and an demo with sql_mode -> sqlfiddle.com/#!2/68a61/14.. check Filipe Silva's answer how it should be done..

Bojan Dević Over a year ago

Table t and t1 are linked with one to many relation, by using id from table t problem descibed in the dbsquare post will never occur and ONLY_FULL_GROUP_BY is turned off by default.

Collectives™ on Stack Overflow

Mysql query using inner join

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related