CODEWITHSUNDEEP
phpmysql
user7347588
user7347588

Reputation:

Get joined datas during a MySQL query with PHP

My environment:

I have these tables:

Table ___Billing:

|----------|----------|----------|--------------|---------------------|
| BIL_Id   | BIL_Item | BIL_Rate | BIL_Quantity | BIL_ApplicableTaxes |
|----------|----------|----------|--------------|---------------------|
| 1        | Hot-Dog  | 4.50     | 2            | 3                   |
| 2        | Tea      | 3.25     | 3            | 3,4                 |
|----------|----------|----------|--------------|---------------------|

BIL_Id = the ID for this item in this table.
BIL_Item = the id of the item (referring to the ___SalesTaxes table).
BIL_Quantity = the quantity of the item the customer used.
BIL_ApplicableTaxes = the id of the applicable taxes for this item. Each taxe id is comma separated.

Table ___SalesTaxes:

|----------|--------------|------------|
| STX_Id   | STX_TaxeName | STX_Amount |
|----------|--------------|------------|
| 3        | Tax 1        | 3.50       |
| 4        | Tax 2        | 6.55       |
|----------|--------------|------------|

STX_Id = the ID for this item in this table.
STX_TaxeName = the name of the taxe.
STX_Amount = the amount in percentage of the taxe.

My question:

How is it possible from these two tables, to loop into the ___Billing table in order to get the taxes in percentage I need to applied ?

For example :

Hope the question is clear.

Thanks.

Upvotes: 2

Views: 51

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522501

If you phrase your query correctly, you can use MySQL's FIND_IN_SET() function to match tax IDs agains the CSV list you have in the BIL_ApplicableTaxes column:

SELECT t1.BIL_Id,
       t1.BIL_Rate,
       GROUP_CONCAT(COALESCE(t2.STX_Amount, 'NA')) AS tax_due
FROM ___Billing t1
LEFT JOIN ___SalesTaxes t2
    ON FIND_IN_SET(t2.STX_Id, t1.BIL_ApplicableTaxes) > 0
GROUP BY t1.BIL_Id

However, you should seriously consider normalizing the ___Billing table and removing the CSV data. Instead, each tax entry should have its own record.

Demo here:

SQLFiddle

Upvotes: 2

Related Questions