User97798 User97798 - 4 months ago 10
SQL Question

How to get value from multiple tables but the value should be min and group wise

How to get value from multiple Table that should be minimum and group wise

Like:
I have Three tables

Table1 Table3
| ID | GPN | PKGID | | GPN | Amt |
| 1 | A | PKG01 | | A | 10 |
| 2 | B | PKG02 | | A | 15 |
| 3 | C | PKG03 | | A | 20 |
| 4 | D | PKG04 | | A | 25 |
| A | 65 |
| A | 30 |
----------------------------- | B | 17 |
| D | 90 |
| B | 20 |
Table2 | B | 40 |
| GPN | Date | | D | 60 |
| A | 2016-09-10 | | B | 80 |
| A | 2016-09-18 | | B | 100 |
| B | 2016-09-10 | | C | 3 |
| B | 2016-09-11 | | C | 6 |
| B | 2016-09-12 | | C | 9 |
| C | 2016-10-12 | | C | 12 |
| C | 2016-10-13 | | C | 15 |
| C | 2016-10-14 | | D | 7 |
| D | 2016-09-10 | | D | 10 |
| D | 2016-10-13 | | D | 13 |


And i have to get values from three of table so how can i do that.

For Example

Date = 2016-09-10

and i have to get values behalf of that
so result should contain all groups min value.

As Result

| ID | PKGID | GPN | Amt | Date |
| 1 | PKG01 | A | 10 | 2016-09-10 |
| 2 | PKG02 | B | 17 | 2016-09-10 |
| 4 | PKG04 | D | 7 | 2016-09-10 |

Answer

Should be this

select table.id, table1.PKGID, table1.GPN, min(table3.Amt), table2.`date`
from table1 
inner join table3 on table1.GPN= table3.GPN
inner join table2 on table1.GPN= table2.GPN
where date(table2.date )  = str_to_date('2016-09-10', '%Y-%m-%d') 
group by table1.GPN, table.id, table1.PKGID, table2.`date`