CODEWITHSUNDEEP
sqloracle10g
SQL person
SQL person

Reputation: 9

SQL - Joining 2 tables with 1 key each, but want specific results

I have 2 tables with a join on 1 key. I only have 1 key on each table. Please help me get the expected results, if possible. thanks! Hi, I have this table, with key SETTLE DT:

SETTLE DT    FUND     DEPTID     PGM     CLASS    ACCT       PROJECT ID   BUD REF   FY     EMPLID       AMOUNT      AMOUNT1   AMOUNT ALL
9/1/2017     21000    5066000    32000    12900    512001    304421850    072017    2017    000015034    1094.51    1094.51    1094.51
9/15/2017    21000    5066000    32000    12900    512001    304421850    072017    2017    000015034    2189.01    2689.01    2689.01
9/15/2017    21000    5066000    10001    11000    512001    500751850    072017    2017    000015034    500        2689.01    2689.01

joining to this table, with key CHECK DT:

NAME            EMPLID       GROSS       BEN PLAN     EMPLYE SUR   PAY END DT   CHECK DT    EMPLYR SUR  NAME         SEC   RUN ID  
Jones Rosie     000015034    250         SUR2    SURS    20        8/19/2017    9/15/2017    31.15    Jones Rosie     5    1719
Jones Rosie     000015034    250         SUR2    SURS    20        9/2/2017     9/15/2017    31.15    Jones Rosie     5    1719
Jones Rosie     000015034  1851.13       SUR2    SURS    148.09    9/2/2017     9/15/2017    230.65   Jones Rosie     5    1719

this sql returns this result:

SELECT 
z.name
,z.emplid
,z.ssn
,z.gross_amt        
,z.benefit_plan
,z.z_employee_surs        
,z.pay_end_dt
,z.check_date       
,ROUND(z_employer_surs,2) 
,x.fund_code
,x.deptid
,x.program_code
,x.class_fld    
,x.account
,x.project_id
,x.budget_ref
,x.amount                 
,x.z_amount_all           
,ROUND((z.z_employer_surs * (x.amount/x.z_amount_all)),2) 
,z.name_display
,z.z_campus
,x.settlement_dt
,z.RUN_ID 
FROM PS_Z_SURS_HR_WK z,
  PS_Z_SURS_JRNL_WK x
WHERE z.emplid   = x.emplid
AND z.emplid = '000015034'
AND z.check_date   = x.settlement_dt
and x.project_id IN ('500751850', '304421850') 
AND x.budget_ref = '072017';

-- this is what i get

NAME            EMPLID       GROSS      BEN PL  EMPLE SUR  PAY END DT   CHECK DT    EMPLYR SUR FUND    DEPTID     PRGM     CLASS    ACCT      PROJECT
Jones Rosie     000015034    1829.13    SUR2    146.33     8/19/2017    9/1/2017    227.91    21000    5066000    32000    12900    512001    304421850
Jones Rosie     000015034    250        SUR2    20         8/19/2017    9/15/2017    31.15    21000    5066000    32000    12900    512001    304421850
Jones Rosie     000015034    250        SUR2    20         9/2/2017     9/15/2017    31.15    21000    5066000    32000    12900    512001    304421850
Jones Rosie     000015034    1851.13    SUR2    148.09     9/2/2017     9/15/2017   230.65    21000    5066000    32000    12900    512001    304421850
Jones Rosie     000015034    250        SUR2    20         8/19/2017    9/15/2017    31.15    21000    5066000    10001    11000    512001    500751850
Jones Rosie     000015034    250        SUR2    20         9/2/2017     9/15/2017    31.15    21000    5066000    10001    11000    512001    500751850
Jones Rosie     000015034    1851.13    SUR2    148.09     9/2/2017     9/15/2017   230.65    21000    5066000    10001    11000    512001    500751850

-- But this is what I want

NAME            EMPLID       GROSS      BEN PL  EMPLE SUR  PAY END DT   CHECK DT    EMPLYR SUR FUND    DEPTID     PRGM     CLASS    ACCT      PROJECT
Jones Rosie     000015034    1829.13    SUR2    146.33     8/19/2017    9/1/2017    227.91    21000    5066000    32000    12900    512001    304421850
Jones Rosie     000015034    1851.13    SUR2    148.09     9/2/2017     9/15/2017   230.65    21000    5066000    32000    12900    512001    304421850
Jones Rosie     000015034    250        SUR2    20         8/19/2017    9/15/2017    31.15    21000    5066000    10001    11000    512001    500751850
Jones Rosie     000015034    250        SUR2    20         9/2/2017     9/15/2017    31.15    21000    5066000    10001    11000    512001    500751850

Thanks!!!

Upvotes: 0

Views: 29

Answers (3)

SQL person
SQL person

Reputation: 9

Thanks, guys, for the input. It turns out that the result set IS the result I want, as confirmed by the users. The problem here is that there is not a 2nd key on both tables to produce the result I expected IE If project id was on the 2nd table.

Upvotes: 0

Hogan
Hogan

Reputation: 70513

You have two rows in your first table with the same settle date and the same employee id.

So when you join to the other table you will join to all 3 rows for both those rows. This gives you 2 times 3 or 6 result rows.

If you only want 3 rows you need to figure out another filter on the first table to give you only one row -- eg PGM = 32000 and add that to your where clause.

Upvotes: 1

ivan.rosina
ivan.rosina

Reputation: 408

I think EmployeeID and Date is not a primary key so when you join the two tables you get combination of values. You should group data by project_id.

NB Columns in select in this case must use aggregate functions such as max(z.name) for example

Upvotes: 0

Related Questions