Solved

Multiple joins on different conditions

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

19 Experts available now in Live!

Get 1:1 Help Now