joining multiple tables

Question

0.00/5 (No votes)

See more:

Hi,

Anybody can help? I am joining four tables to get the required result. Referring to the first script below, I want to get the summary of Charges which is one of the fields of table1.SUM value should be grouped by month according to the adddate field from table4. How can i change of below script to get the expected result.

Hope anyone can help me. :)

SQL

SELECT * FROM Table1 WHERE Table1.evidencenumber IN 
(SELECT DISTINCT table2.evidencenumber FROM table3 rs, table2
WHERE table3.rep_id IN (SELECT DISTINCT rep_id FROM table4 WHERE adddate>=TO_DATE('010109','ddmmyy') 
AND adddate<=TO_DATE('311209','ddmmyy') AND dept='Engineering')
AND table3.evidencenumber=table2.evidencenumber
AND table2.pareantid IS NULL)

Posted 22-May-11 23:38pm

phoopwint

Updated 22-May-11 23:51pm

Keith Barrow

v2

Add a Solution

Comments

udayakumarcgvak 12-Oct-11 3:54am

Helo Friend,

Your Sql Code is Not clear to me,So U give Table & Field details and Your Requirement.I think that is good for some one understand..

regards
Udayakumar.P

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)

Mirzakhmet Syzdykov · Answer 1 · 2012-06-20T02:59:00

If I correctly understand the purpose you need only summary by the values of Table1, then you can filter distinct results by simply joining all the subqueries in outer "where" clause and adding the "rowid" marker of the row from corresponding table (this is possible in Oracle), and thus calculating the result in the outer query:

SQL

select T.adddate, sum(T.Charges) from
(
SELECT distinct Table1.rowid, Table1.Charges, table4.adddate FROM
  Table1,
  table3,
  table2,
  table4
WHERE
 Table1.evidencenumber = table2.evidencenumber 
 AND table3.rep_id = table4.rep_id
 and table4.adddate>=TO_DATE('010109','ddmmyy') 
 AND table4.adddate<=TO_DATE('311209','ddmmyy')
 AND table4.dept='Engineering'
 AND table3.evidencenumber=table2.evidencenumber
 AND table2.pareantid IS NULL
) T 
group by T.adddate

Still if your SQL engine doesnt have the built-in row marker (like rowid in Oracle) you can add a unique field to the Table1 and use it as a rowid-marker.