本文介绍了如何从mysql中的多个表中获取记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有以下表格

i have the follwing tables

cust_info{cust_id,cust_name}
ordertable{oid,cust_id,order_qty,order_amount,order_date}
orderdetails{oid,productname,quantity,amount}
invoices{inv_id,oid,invoice_qty,inv_amt,inv_date}
invoicedetails{inv_id,productname,qty,amountt}



当我点击特定客户我需要结果为


when i click on particular customer i need results as

Result{Productname,order_qty,invoice_qty,order_amount,inv_amt,order_date,inv_date}



为此我使用了以下查询


for this i have used the follwing query

select id.productname,quantity order_qty,invoice_qty,(quantity-qty) as pending_qty,od.amount order_amt,id.amount inv_amt,(od.amount-id.amount) as pending_amt,date order_date,inv_date from ordertable ot join orderdetails od on ot.oid=od.oid join invoices inv on ot.oid=inv.oid join invoicedetails id on inv.inv_id=id.inv_id where ot.cust_id=1 group by id.productname



但是对于order_qty列,它对两个记录采用相同的值。喜欢


but for order_qty column it takes same value for two records. like

(productname, order_qty, invoice_qty,  order_amt, inv_amt,  order_date, inv_date) VALUES
('Obact-200mg 10 X10 Tab', 20, 20, 2200, 2200,    '10/18/2013 6:14:51 PM', '10/24/2013 5:50:46 PM'),
('Obact-OZ 10 X10 Tab', 20, 20,2200, 2800, '10/18/2013 6:14:51 PM', '10/24/2013 5:50:46 PM')



for order_qty(20,30)和order_amt(2200,3600)两个记录的值相同



如何查询我的查询以获取正确的记录请帮助我out。


for order_qty (20,30) and order_amt(2200,3600) it takes same value for both records

how can i chage my query to get the correct records please help me out.

推荐答案


这篇关于如何从mysql中的多个表中获取记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-10 22:10