mysql fetch a column value from another table in existing query

Question

I have three MySQL tables, successfully have completed a query which does some calculations on payments table to be displayed against orders.

Question: Need to fetch a full_name column from sku_mapping table in below mention query join condition is

concat ('f|',fk_orders.sku) = sku_mapping.prefix_sku

fk_orders

|order_item_id |order_id    |Invoice_No       |Invoice_No_Amt  |Qty   |Refund_Qty |Refund_Amount | sku
------------------------------------------------------------------------------------------------------
|1131231       |123         |F08OTTN16-1      |100            |1     |            |              |A3001
|1113138       |321         |F08OTTN16-2      |200            |2     |1           |200           |B1001
|1231231       |023         |F08OTTN16-3      |100            |1     |1           |100           |C2001
|1133138       |320         |F08OTTN16-4      |200            |2     |            |              |D8901
|1134231       |103         |F08OTTN16-5      |100            |1     |            |              |E6210
|1113538       |300         |F08OTTN16-6      |200            |2     |            |              |F1001
|1003538       |300         |F08OTTN16-7      |200            |2     |            |              |G9003

fk_payments

|order_item_id    |order_id    |Invoice_No       |Invoice_No_Amt |Settlement_Value
-----------------------------------------------------------------------------------
|OI:1131231       |123         |F08OTTN16-1      |100            |40
|OI:1113138       |321         |F08OTTN16-2      |200            |150
|OI:1231231       |023         |F08OTTN16-3      |100            |-50
|OI:1133138       |320         |F08OTTN16-4      |200            |200
|OI:1134231       |103         |F08OTTN16-5      |100            |40
|OI:1113538       |300         |F08OTTN16-6      |200            |250
|OI:1131231       |123         |F08OTTN16-1      |100            |40
|OI:1133138       |320         |F08OTTN16-4      |200            |100
|OI:1113138       |321         |F08OTTN16-2      |200            |-200

sku_mapping

|prefix_sku |full_name 
-------------------
x|A3001     |Apple_Phone
f|B1001     |Belkin
f|C2001     |Cat_Access
f|D8901     |Dlink
f|E6210     |Eltron
f|F1001     |Flag
f|G9003     |gott
f|A3001     |Phone
a|B1001     |Belkin
a|C2001     |Cat_Access
a|D8901     |Dlink
a|E6210     |Eltron
a|F1001     |Flag
a|G9003     |gott

Current Result

|order_item_id  |order_id   |Invoice_No     |Invoice_No_Amt |Qty    |Refund_Qty |Refund_Amount  |sku    |SettledAmount  |netAmount
------------------------------------------------------------------------------------------------------------------------------------
|1131231        |123        |F08OTTN16-1    |100            |1      |           |               |A3001  |80             |20
|1113138        |321        |F08OTTN16-2    |200            |2      |1          |200            |B1001  |150            |50
|1231231        |23         |F08OTTN16-3    |100            |1      |1          |100            |C2001  |50             |50 
|1133138        |320        |F08OTTN16-4    |200            |2      |           |               |D8901  |300            |-100
|1134231        |103        |F08OTTN16-5    |100            |1      |           |               |E6210  |40             |60
|1113538        |300        |F08OTTN16-6    |200            |2      |           |               |F1001  |250            |-50
|1003538        |300        |F08OTTN16-7    |200            |2      |           |               |G9003  |0              |200

Expected Result (need full_name column from sku_prefix table)

|order_item_id  |order_id   |Invoice_No     |Invoice_No_Amt |Qty    |Refund_Qty |Refund_Amount  |sku    |SettledAmount  |netAmount  |full_name
-----------------------------------------------------------------------------------------------------------------------------------------------
|1131231        |123        |F08OTTN16-1    |100            |1      |           |               |A3001  |80             |20         |Apple_Phone
|1113138        |321        |F08OTTN16-2    |200            |2      |1          |200            |B1001  |150            |50         |Belkin
|1231231        |23         |F08OTTN16-3    |100            |1      |1          |100            |C2001  |50             |50         |Cat_Access
|1133138        |320        |F08OTTN16-4    |200            |2      |           |               |D8901  |300            |-100       |Dlink
|1134231        |103        |F08OTTN16-5    |100            |1      |           |               |E6210  |40             |60         |Eltron
|1113538        |300        |F08OTTN16-6    |200            |2      |           |               |F1001  |250            |-50        |Flag
|1003538        |300        |F08OTTN16-7    |200            |2      |           |               |G9003  |0              |200        |gott

Current Code

select o.*,
       (coalesce(Refund_Amount, 0) + coalesce(sv, 0)) as SettledAmount,
       (Invoice_No_Amt - coalesce(Refund_Amount, 0) - coalesce(sv, 0)) as netAmount
from fk_orders o left join
     (select invoice_no, sum(Settlement_Value) as sv
      from fk_payments
      group by invoice_no
     ) p
     on o.invoice_no = p.invoice_no;

How are you pulling back sku currently? there is no mention of it in the code, or of it being in the fk_orders table? — Matt
– Matt, Commented Feb 17, 2016 at 9:13
@Matt that is what I what to do. I guess I need to add the below code but do no how where to place it FROM fk_orders,sku_mapping WHERE concat ('f|',fk_orders.sku)=sku_mapping.prefix_sku — Kanted
– Kanted, Commented Feb 17, 2016 at 9:16
Ok, you should have mentioned there was an fk_orders.sku in the fk_orders table sample data above — Matt
– Matt, Commented Feb 17, 2016 at 9:18

Matt · Accepted Answer · 2016-02-17 09:16:12Z

1

Assuming fk_orders has a sku field.

SELECT o.*, sk.full_name, (coalesce(Refund_Amount, 0) + coalesce(sv, 0)) AS SettledAmount, (Invoice_No_Amt - coalesce(Refund_Amount, 0) - coalesce(sv, 0)) AS netAmount
FROM fk_orders o 
LEFT JOIN (SELECT invoice_no, SUM(Settlement_Value) AS sv
           FROM fk_payments
           GROUP BY invoice_no) p ON o.invoice_no = p.invoice_no
INNER JOIN sku_mapping sk ON o.sku = SUBSTRING_INDEX(prefix_sku,'|',-1)

answered Feb 17, 2016 at 9:16

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kanted Over a year ago

it worked perfectly. Have edited orders table. I had earlier missed to add the sku column

Kanted Over a year ago

the above code has issues it had worked with test table. The actual prefix is "f|" I did replace it SUBSTRING_INDEX(prefix_sku,'f|',-1) but the output result invoice sequence is hanged. Also I have about 40000 rows in each table. And each table has many other columns which are not listed above question since just wanted to look tidy. without the innerjoin the code works for 40 K rows too. But if I add the inner join code no result. i have made a small change in the sku_table so you replicate it at your place

Matt Over a year ago

it doesnt matter what is before the |, it could be abc|E3001 and the same code would pick out E3001

Kanted Over a year ago

need to join only if the prefix is f| (f with pipe) if the changes, Now i have made changes to sample sku_mapping table and the result is not sorted with invoice no. Thanks

Collectives™ on Stack Overflow

mysql fetch a column value from another table in existing query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related