Sky Web Centre Sky Web Centre - 6 months ago 12
SQL Question

Code to fetch Data from 2 tables & enter in 3rd Table in php

Table1 - Payout Requests
`

|--------|-----------|-----------|
| id | Receiver | Balance |
|--------|-----------|-----------|
| 1 | user1 | 2000 |
|--------|-----------|-----------|
| 2 | user2 | 1500 |
|--------|-----------|-----------|`




Table2 - Donars

`
|--------|-----------|-----------|
| id | Donar | Amount |
|--------|-----------|-----------|
| 1 | love | 1500 |
|--------|-----------|-----------|
| 2 | don1 | 1000 |
|--------|-----------|-----------|`




Now my question; What piese of code will make Adjustments of both the tables so that data from these two tables goes in 3rd table in the following way


Table3 - Links
`

|--------|-----------|-----------|----------|
| id | Donar | Receiver | Amount |
|--------|-----------|-----------|----------|
| 1 | love | user1 | 1500 |
|--------|-----------|-----------|----------|
| 2 | don1 | user1 | 500 |
|--------|-----------|-----------|----------|
| 3 | don1 | user2 | 500 |
|--------|-----------|-----------|----------|`





AND now Table 1 & Table 2 will be Follows -

Table1 - Payout Requests
`

|--------|-----------|-----------|
| id | Receiver | Balance |
|--------|-----------|-----------|
| 2 | user2 | 1000 |
|--------|-----------|-----------|`




Table2 - Donars
`

|--------|-----------|-----------|
| id | Donar | Amount |
|--------|-----------|-----------|
| Nothing |
|--------|-----------|-----------|`

Answer

In the first step you should have a foreign key on Donars table which save id of Payout Requests. for example pr_id

You can use INNER JOIN like this:

SELECT `t2`.`id`, `t2`.`Donar`, `t1`.`Receiver `, `t1`.Balance - `t2`.`Amount` AS `Adjustment` FROM `table1` AS `t1` INNER JOIN `table2` AS `t2` ON `t2`.`pr_id` = `t1`.id