[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Multiple joins on different conditions

Posted on 2009-03-31
5
Medium Priority
?
423 Views
Last Modified: 2012-05-06
I need to join different tables on different conditions and group by
select id,
       pdate,
       ptype,
       count(*),
       sum(amount),
 
 from payment PA
   computer  ba,
   network  ca,
   other da
where PA.pdate  BETWEEN TRUNC(sysdate) AND TRUNC(sysdate) + .99999
 
  need to join with computer table if ptype =computer 
  and pa.id=ba.id(+)
 
  need to join with netork  table if ptype =network
  and pa.id=ca.id(+)
 
  need to join with other table if ptype =other
  and pa.id=da.id(+)
 
group by id,pdate,ptype

Open in new window

0
Comment
Question by:mikky7
  • 2
  • 2
5 Comments
 
LVL 3

Expert Comment

by:techmaza
ID: 24029372

u need to use procedures or function where u can pass ptype as parameter and use IF ELSE block to get the required select result.

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24029402
try this:
select PA.id,
       PA.pdate,
       PA.ptype,
       count(*),
       sum(PA.amount),
from payment PA
LEFT join computer ba on pa.id=ba.id and ptype = 'computer' 
LEFT join network  ca on pa.id=ca.id and ptype ='network'
LEFT join other da on pa.id=da.id and ptype ='other'
where PA.pdate  BETWEEN TRUNC(sysdate) AND TRUNC(sysdate) + .99999
group by PA.id,PA.pdate,PA.ptype

Open in new window

0
 

Author Comment

by:mikky7
ID: 24029413
Thanks,
So it means not possible to do in one query because this would be part of existing report in perl  
I am looking just one query.
Thanks very much again
0
 
LVL 3

Accepted Solution

by:
techmaza earned 375 total points
ID: 24066623
 if u think my answer is correct and u want to close this topic then u shuld either accept the solution or comment for the solution and wait for another solution.



0
 

Author Comment

by:mikky7
ID: 24092068
Thanks so much and it was really good experience which is fast and accurate

I do no know why we should grade this because eveyone is learning here
Thanks again techmaza:
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question