How to fetch multiple values from the table based on foreign key?

Question

I have two tables,

#table 1 contains the following data structure

NO category_name    category_code   amount   type
1  provident_fund       PF           1000   Deduction
2  Home Allowance       HM           12000  Earning
3  Basic_pay            BP           35000  Earning

#table 2 contains the following data structure

NO group_name         payroll_cat_name 
1  permanent          PF,HM,BP,TX
1  visiting           HM,BP

from the table 2, I can get the value of payroll_cat_name using the query

$a = "Select payroll_cat_name from table_2";

using the above query, I'm getting the value PF,HM,BP,TX.

Now, I have to use this multiple values PF,HM,BP,TX in order to get the sum amount from table 1.

Following is my code that I have tried,

include "../db.php";
$a = "Select payroll_cat_name from table_2";
$b = mysqli_query($con,$a);
$c = mysqli_fetch_array($b);
$d = $c["payroll_cat_name"];
echo "$d";
    $myArray = explode(',', $d);
    
print_r($myArray);
$tr = "select SUM(amount) as am from table_1 where category_code in ($d)";

$rt = mysqli_query($con,$tr);
$new = mysqli_fetch_array($rt);
$gh = $new["am"];

Normalize the schema, see ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). — sticky bit, Dec 19 '20 at 05:53

GMB · Accepted Answer · 2020-12-19T20:37:42.553

0

That’s a poor data model. There should be a separate table to store the relation between groups and categories, which each tuple on a different row. The linked commented by sticky bit on the question gives great explanation on how and why.

For your current structure, one option using uses find_in_set() and a subquery:

select t2.*,
    (
        select sum(t1.amount)
        from table_1 t1
        where find_in_set(t1.category_code, t2.payroll_cat_name)
    ) as t1_amount
from table_t2 t2

edited Dec 19 '20 at 20:37

answered Dec 19 '20 at 07:24

GMB

216,147
25
84
135

Thank you for your replay, I have tried this on my sql prompt ```select t2.*, (select sum(t1.amount) from payroll_categrie t1 where find_in_set(t2.payroll_cat_name, t1.payroll_cat_code) ) as t1_amount from payroll_group t2``` but it's not working, at t1_amount coloum it is showing Null – pal deepak Dec 19 '20 at 17:36
Thank you sir ```select t2.*,(select sum(t1.amount) from payroll_categrie AS t1 where find_in_set(t1.payroll_cat_code,t2.payroll_cat_name)) as t1_amount from payroll_group t2 ``` By changing the places of parameter inside find_in_set i got the answer – pal deepak Dec 19 '20 at 18:00
@paldeepak: ah yes, my bad. I fixed that in the answer as well. – GMB Dec 19 '20 at 20:39

How to fetch multiple values from the table based on foreign key?

1 Answers1