I hate joins... 2

[THoey]

I am helping someone with an ugly join (well, they are all ugly to me). They have two tables with payroll data that they need to join together in a report. The following are the tables:

TR_PAYLIST
   Name             Null?    Type
   PERIODID         NOT NULL NUMBER
   SSN              NOT NULL VARCHAR2(9)
   NAMELAST                  VARCHAR2(30)
   NAMEFIRST                 VARCHAR2(25)
   NAMEM                     VARCHAR2(15)
   ADPFILENO                 NUMBER
   ADPDEPTID                 VARCHAR2(10)
   ADPBATCHID                VARCHAR2(10)
   DEPTID                    VARCHAR2(10)
   MANID1                    VARCHAR2(10)
   MANID2                    VARCHAR2(10)
   ADDED                     DATE
   ADDEDBY                   VARCHAR2(15)
   UPDATED                   DATE
   UPDATEDBY                 VARCHAR2(15)
   CANCELPAY                 CHAR(1)

TR_PAYDETAIL
   Name             Null?    Type
   PERIODID                  NUMBER
   SSN                       VARCHAR2(9)
   PDDATE                    DATE
   PAYCODE                   VARCHAR2(10)
   ADPCODE                   VARCHAR2(10)
   AMOUNT                    NUMBER(9,4)
   TEMPRATE                  NUMBER(9,4)
   PDNOTES                   NCLOB
   ADDED                     DATE
   ADDEDBY                   VARCHAR2(15)
   UPDATED                   DATE
   UPDATEDBY                 VARCHAR2(15)

What he needs is a SELECT statement that would return the following fields:

TR_PAYLIST.ADPFILENO, 
TR_PAYDETAILS.ADPCODE, 
TR_PAYDETAILS.PAYCODE, 
TR_PAYDETAILS.TEMPRATE,
SUM(TR_PAYDETAILS.AMOUNT) AS amt

The wrenches in this are as follows:
1. Join the tables on PERIODID's and SSN's
2. Return data from every row of TR_PAYLIST and that data from TR_PAYDETAILS that corresponds to it (Left Join?).
3. GROUP BY ADPFILENO, TEMPRATE, ADPCODE, PAYCODE
4. SUM the AMOUNT column

Any assistance would be greatly appreciated. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ljspop]

It sounds like a simple join to me?

where TR_PAYLIST.PERIODID = TR_PAYDETAILS.PERIODID
and TR_PAYLIST.SSN = TR_PAYDETAILS.SSN

If you need to do it as an outer join, that is to say there is parent columns
with no detail/children rows associated, add the outer join syntax:

where TR_PAYLIST.PERIODID = TR_PAYDETAILS.PERIODID(+)
and TR_PAYLIST.SSN = TR_PAYDETAILS.SSN(+)

I would check the details of these tables before adding the
outer join to see if there are parents without children before
using it. Would the people receiving the report expect there
to be a paylist record without any paydetails? There is a
performance hit associated with outer joins.

Good luck,

Bob
Bob Lowell
ljspop@yahoo.com

 

[THoey]

Thanks for the suggestions. I think the person I am trying to help scared me a bit with his "this is what I am thinking" query (realize, he has put some literals in to limit the data):

SELECT TR_PAYLIST.ADPFILENO, TR_PAYDETAILS.ADPCODE, TR_PAYDETAILS.PAYCODE,
TEMPRATE, SUM(AMOUNT) AS TOTALAMT
FROM   TR_PAYDETAILS, TR_PAYLIST
WHERE  TR_PAYDETAILS.PERIODID = TR_PAYLIST.PERIODID AND
       TR_PAYDETAILS.SSN      = TR_PAYLIST.SSN      AND
       TR_PAYDETAILS.PERIODID = 200101              AND
       TR_PAYLIST.PERIODID    = 200101              AND
       TR_PAYLIST.ADPDEPTID   = 'N01080'
GROUP BY ADPFILENO, TEMPRATE, ADPCODE, PAYCODE
--ORDER BY ADPFILENO, TEMPRATE
   UNION
SELECT TR_PAYLIST.ADPFILENO, NULL AS ADPCODE, NULL AS PAYCODE, NULL AS
TEMPRATE, NULL AS TOTALAMT
FROM   TR_PAYLIST
WHERE  TR_PAYLIST.PERIODID  = 200101   AND
       TR_PAYLIST.ADPDEPTID = 'N01080' AND
       SSN NOT IN (SELECT TR_PAYLIST.SSN
                  FROM   TR_PAYDETAILS, TR_PAYLIST
                   WHERE  TR_PAYDETAILS.PERIODID = TR_PAYLIST.PERIODID AND
                          TR_PAYDETAILS.SSN      = TR_PAYLIST.SSN      AND
                          TR_PAYDETAILS.PERIODID = 200101              AND
                          TR_PAYLIST.PERIODID    = 200101              AND
                          TR_PAYLIST.ADPDEPTID   = 'N01080'
                   GROUP BY TR_PAYLIST.SSN);

As to whether or not there may be records in paylist without children in paydetail, the answer is yes.

Again, thanks for all possible solutions... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[MikeJones]

I still think Bob's idea holds water with the outer joins in place, I don't see what he's trying to achieve with the union and the not in within it. What is this supposed to limit?

SELECT TR_PAYLIST.ADPFILENO, TR_PAYDETAILS.ADPCODE,
TR_PAYDETAILS.PAYCODE,
TEMPRATE, SUM(AMOUNT) AS TOTALAMT
FROM TR_PAYDETAILS,
TR_PAYLIST
WHERE TR_PAYDETAILS.PERIODID (+) = TR_PAYLIST.PERIODID
AND TR_PAYDETAILS.SSN (+) = TR_PAYLIST.SSN
GROUP BY ADPFILENO, ADPCODE, PAYCODE, TEMPRATE

What's wrong with the resulting output?

 

[THoey]

You guys are great. Like I said, I think his query through me in the wrong direction.

Thanks again... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.