Solved

Multiple joins on different conditions

Posted on 2009-03-31
5
416 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 125 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error converting data type nvarchar to bigint. 2 69
Query syntax 10 40
Help with SQL field formatting 3 20
SQL Union 20 44
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

919 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now