本文介绍了如何从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中的多个表中获取记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!