how to get records from multiple table in mysql

Question

0.00/5 (No votes)

See more:

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

SQL

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

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) 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.

Posted 24-Oct-13 20:41pm

Member 10345959

Updated 24-Oct-13 20:48pm

Prasad Khandekar

v2

Add a Solution

Comments

Prasad Khandekar 25-Oct-13 2:56am

Can you share the sample data in these tables?

Member 10345959 25-Oct-13 3:26am

(cust_id, cust_name, address, ContactPerson, mail, phone, vat_tin) VALUES
(1, 'M/s Sri baba Enterprices'),
(2, 'Madhav pharma')

ordertable
----------
(oid,order_qty,order_amount,order_date) VALUES
(1, 6090, 40, '10/18/2013 6:14:51 PM'),
(2, 8000, 100, '10/22/2013 11:54:24 AM')

orederdetails
-------------

(oid,productname,quantity,amount) VALUES
(1, 'Obact-200mg 10 X10 Tab', 20, 2200),
(2, 'Obact-OZ 10 X10 Tab', 30, 3600),
(3, 'Obact-400mg 10 X10 Tab', 50, 40000),
(4, 'DFS+ 20x10 Tab', 50, 4000)

invoices
--------
(inv_id,oid,invoice_qty,inv_amt,inv_date ) VALUES
(1, 1, 20, 1000, '10/24/2013 5:50:46 PM'),
(2, 2, 90, 6000, '10/24/2013 5:50:59 PM')

invoicedetails
---------------
(inv_id,productname,qty,amountt) VALUES
(1, 'Obact-200mg 10 X10 Tab', 2200, 10),
(2, 'Obact-400mg 10 X10 Tab', 2000, 40),
(2, 'DFS+ 20x10 Tab', 4000, 45),
(1, 'Obact-OZ 10 X10 Tab', 2800, 10)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vishnu Prajapati · Answer 1 · 2013-10-24T21:10:00

Solution 1

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

Posted 24-Oct-13 21:10pm

Vishnu Prajapati

Updated 24-Oct-13 21:31pm

v3

Comments

Member 10345959 25-Oct-13 3:15am

sorry my result shoud be
Result{Productname,quantity,qty,amount,amountt,order_date,inv_date}