| Adding (Add - Subtract) values from two different tables to another table [message #182420] |
Sun, 04 August 2013 03:54  |
vikas daiya
Messages: 2
Registered: August 2013
Karma: 0
|
Junior Member |
|
|
am developing inventory page in php with database as mentioned below. I have some complex equation which I am unable to resolve. Please help me out.
I have one table name "A" with column X and Y having values as follows:
Table "A"
------------
X Y
------------
blue 0
yellow 0
brown 0
Table "B" in following pattern.
------------
X Y
------------
blue 50
yellow 15
blue 20
brown 5
similarly Table "C"
------------
X Y
------------
blue 15
yellow 10
blue 5
brown 5
Now what I want if I add record in table "B" as mention above it should be updated in table "A" with following equation.
"A"= "B"-"C"
result should be i.e
Table "A"
------------
X Y
------------
blue 20
yellow 5
brown 0
same in case of adding in records table "C" equation should be as follows:
please note X values in table "A" are DISTINCT where as in other table are not.
|
|
|
|
| Re: Adding (Add - Subtract) values from two different tables to another table [message #182421 is a reply to message #182420] |
Sun, 04 August 2013 04:31  |
Daniel Pitts
Messages: 68
Registered: May 2012
Karma: 0
|
Member |
|
|
On 8/3/13 8:54 PM, vikas daiya wrote:
> am developing inventory page in php with database as mentioned below. I have some complex equation which I am unable to resolve. Please help me out.
>
> I have one table name "A" with column X and Y having values as follows:
>
> Table "A"
>
> ------------
> X Y
> ------------
> blue 0
> yellow 0
> brown 0
> Table "B" in following pattern.
>
> ------------
> X Y
> ------------
> blue 50
> yellow 15
> blue 20
> brown 5
> similarly Table "C"
>
> ------------
> X Y
> ------------
> blue 15
> yellow 10
> blue 5
> brown 5
> Now what I want if I add record in table "B" as mention above it should be updated in table "A" with following equation.
>
> "A"= "B"-"C"
>
> result should be i.e
>
> Table "A"
>
> ------------
> X Y
> ------------
> blue 20
> yellow 5
> brown 0
> same in case of adding in records table "C" equation should be as follows:
>
> please note X values in table "A" are DISTINCT where as in other table are not.
>
This sounds more like a SQL problem then PHP.
Try something along the lines:
select sum(B.y) - sum(C.y) from B, C on B.x=C.x group by B.x
|
|
|
|
| Re: Adding (Add - Subtract) values from two different tables to another table [message #182428 is a reply to message #182421] |
Sun, 04 August 2013 13:47 |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 8/4/2013 12:31 AM, Daniel Pitts wrote:
> On 8/3/13 8:54 PM, vikas daiya wrote:
>> am developing inventory page in php with database as mentioned
>> below. I have some complex equation which I am unable to resolve.
>> Please help me out.
>>
>> I have one table name "A" with column X and Y having values as follows:
>>
>> Table "A"
>>
>> ------------
>> X Y
>> ------------
>> blue 0
>> yellow 0
>> brown 0
>> Table "B" in following pattern.
>>
>> ------------
>> X Y
>> ------------
>> blue 50
>> yellow 15
>> blue 20
>> brown 5
>> similarly Table "C"
>>
>> ------------
>> X Y
>> ------------
>> blue 15
>> yellow 10
>> blue 5
>> brown 5
>> Now what I want if I add record in table "B" as mention above it
>> should be updated in table "A" with following equation.
>>
>> "A"= "B"-"C"
>>
>> result should be i.e
>>
>> Table "A"
>>
>> ------------
>> X Y
>> ------------
>> blue 20
>> yellow 5
>> brown 0
>> same in case of adding in records table "C" equation should be as
>> follows:
>>
>> please note X values in table "A" are DISTINCT where as in other table
>> are not.
>>
> This sounds more like a SQL problem then PHP.
>
> Try something along the lines:
>
> select sum(B.y) - sum(C.y) from B, C on B.x=C.x group by B.x
I agree - this is a database problem, and should be followed up in a
newsgroup related to the database (depending on which database he is
using, there may be multiple options).
However, the fact he needs to update table "a" when table "b" is updated
indicates a severe normalization problem.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]