data fetching from three table in mysql

Question

0.00/5 (No votes)

See more:

hello i am using reportviewer for showing the values from 3 tables.

1.table_sale1 
------------------------------------------------
bill_date |bill_amount|paid_advance|balance_due|
          |           |            |           |
          |           |            |           |
          |           |            |           |
------------------------------------------------

2.table_invoice
-----------------------------
invoice_date |invoice_amount|
             |              |           
             |              |           
             |              |           
-----------------------------

3.table_expenses
-----------------------------
expense_date |expense_amount|
             |              |
             |              |
             |              |
-----------------------------

these are three tables
i need the out put like

---------------------------------------------------------------------------------
date      |bill_amount|paid_advance|balance_due|invoice_amount|expense_amount |
          |           |            |           |              |               | 
          |           |            |           |              |               | 
          |           |            |           |              |               | 
---------------------------------------------------------------------------------

i use the query,i got the output but some values repeated.
how to solve these pblm..?
query is
-------------

SQL

SELECT        table_sale1.bill_date, table_invoice.invoice_date, table_expenses.dates, table_sale1.total_amount, table_sale1.amount_paid, table_sale1.amount_due,
                         table_invoice.total_amount AS Expr1, table_expenses.amount AS Expr2
FROM            table_sale1, table_invoice, table_expenses
GROUP BY table_sale1.bill_date, table_invoice.invoice_date, table_expenses.dates

please help me..........

Posted 5-May-13 9:08am

sabodh

Updated 5-May-13 9:23am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

ridoy 5-May-13 15:55pm

You can use DISTINCT to escape from repeated values

sabodh 7-May-13 12:59pm

i also use distinct but no use..

Maciej Los 5-May-13 16:53pm

Is there any relationship between tables?

sabodh 7-May-13 13:00pm

no. all these tables are for three distinct purposes..may be the dates are same

2 solutions

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)

Maciej Los · Answer 1 · 2013-05-05T19:58:00

First of all, read my comment.

Untill your database is not relational database[^], you can't simply avoid duplicates.

How to create realational database? Creating A Quick MySQL Relational Database[^]

After that you need to learn about JOIN's[^]. To understand differences between LEFT, RIGHT, INNER joins, read this article: Visual Representation of SQL Joins[^]

Have a nice day ;)

Aarti Meswania · Answer 2 · 2013-05-05T20:16:00

Solution 2

If you want datewise summary then,

SQL

SELECT        Date(table_sale1.bill_date) as Date_, sum(table_sale1.total_amount) as bill_amt, sum(table_sale1.amount_paid) as amt_paid, sum(table_sale1.amount_due) as amt_due,
                         sum(table_invoice.total_amount) AS invoice_amt, sum(table_expenses.amount) AS expense_amt
FROM            table_sale1
left join  table_invoice on Date(table_sale1.bill_date) = Date(table_invoice.invoice_date)
left join  table_expenses on Date(table_invoice.invoice_date) = Date(table_expenses.dates)
GROUP BY date(table_sale1.bill_date), date(table_invoice.invoice_date), Date(table_expenses.dates)

Happy Coding!
:)

Posted 5-May-13 20:16pm

Aarti Meswania

Updated 5-May-13 20:25pm

v2

Comments

Maciej Los 6-May-13 2:19am

Aarti, are you sure it is a correct answer? I'm afraid it's not, because of time part in date field. It would be OK, if you reject time part from date field.

Aarti Meswania 6-May-13 2:26am

you are right, Thanks! :)
I have edited my answer.

sabodh 7-May-13 13:12pm

thanks all of you for the given solutions.
actually my purpose for this query is fetching all the amounts from the three tables.
for showing the bill_amount,invoice amount,expense in a particular day.
ie, the current day income and expenditure.
i used all these querys some data are repeated.

Aarti Meswania 8-May-13 0:32am

after group by data should not repeat,
but check it should not a problem of datetime you have use date() function

sabodh 8-May-13 22:48pm

no i am not use the date() fn

Aarti Meswania 9-May-13 0:40am

if you have date time in bill_date /invoce_date/expense_date then it will nt group it so use date fn. as I use in my solution