如何从mysql中的多个表中获取记录 [英] how to get records from multiple table in 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.

推荐答案

选择

od.Productname，od.quantity，id.qty，od.amount，id.amountt，ot。 order_date，iv.inv_date

来自

ordertable as ot inner join orderdetails as od inner join invoices as iv inner join invoicedetails as id on ot.oid = od.oid = iv .oid && od.productName = id.productName

select
od.Productname,od.quantity,id.qty,od.amount,id.amountt,ot.order_date,iv.inv_date
from
ordertable as ot inner join orderdetails as od inner join invoices as iv inner join invoicedetails as id on ot.oid = od.oid = iv.oid && od.productName = id.productName

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