CODEWITHSUNDEEP
mysqlsql
Sagar
Sagar

Reputation: 997

Retrieving records where sum of same columns with some condition

I have two tables, table1 and table2, with same structure. The columns are R, a, b, c, d; a, b, c, d are INTs and R is VARCHAR.

I need all Rs where sum of a in table1 and table2 is less than 40 and same for b, c and d.

The Statement that I executed is:

SELECT table1.R FROM table1,table2 where
table1.a + table2.a <40 or
table1.b + table2.b <40 or 
table1.c + table2.c <40 or 
table1.d + table2.d <40;

But it is giving unexpected results. The number of rows returned are far grater than the sum of number of records of two tables. 

    table1                   table2
R   a   b   c   d            R      a     b    c    d
i1 45  28  29  22           i1      8   20   13    8
i2 28  28  29  30           i2      12   12  16     20 
i2 28  28  10  30           i2      12   12  16     20 
i2 28  5  29  30            i2      12   12  16     20 
i2 28  28  10  30           i2      12   12  16     20 
i2 28  28  29  30           i2      15   15  10     12 
i2 10  12  15  20           i2      8     3  6      12 

expected results 
i1

because table1.d+table2.d <40 for R = i1

Upvotes: 0

Views: 86

Answers (1)

Barmar
Barmar

Reputation: 780724

It's not totally clear, but what you probably want is:

SELECT table1.R FROM table1,table2 where
table1.R = table2.R and
(table1.a + table2.a <40 or
 table1.b + table2.b <40 or 
 table1.c + table2.c <40 or 
 table1.d + table2.d <40);

See this FIDDLE

Upvotes: 1

Related Questions