sql query/oracle

Question

I have a 2 tables Existing_tools and Recommendation_tool having 4 columns (Account, Activity, Tools_name, count) but last column gives me number of existing tools and number of recommended tools count in respective tables.

I want output (Account, Activity, Tools_name, existing_tools_count, recommendation_tools_count), group by account and activity and all tools present in both the tables comes in output.

If tool name is not present in existing table then count will be 0 against existing_tools_count, same way if tool is not present in recommendation table count against recommendation_tools_count count will be 0.

And if tool is present in both the tables then given count will come accordingly.

Existing_TOOLS

Account Activity       tools_Name   Number of Existing_tool
x   Agile/Proj. Mgmt    JIRA             5
x   Agile/Proj. Mgmt    Collabnet        4
x   Build               ANT              3
x   Build               MAVEN            3
y   Agile/Proj. Mgmt    JIRA             5
y   Agile/Proj. Mgmt    Collabnet        4
y   Build               ANT              3
y   Build               MAVEN            3

Recommendation_tool

Account    Activity    tools_Name    Number of recommendation_tool
x    Agile/Proj. Mgmt    JIRA         5
x    Agile/Proj. Mgmt    HP ALM       4
x    Build               MS build     3
y    Agile/Proj. Mgmt    JIRA         5
y    Agile/Proj. Mgmt    HP ALM       4
y    Build               MS build     3

Required_TABLE

Account    Activity    tools_Name    Number of Existing_tool    Number of recommendation_tool
x    Agile/Proj. Mgmt    JIRA                5                         5
x    Agile/Proj. Mgmt    Collabnet           4                         0
x    Agile/Proj. Mgmt    HP ALM              0                         4
x    Build               ANT                 3                         0
x    Build               MAVEN               3                         0
x    Build               MS build            0                         3
y    Agile/Proj. Mgmt    JIRA                5                         5
y    Agile/Proj. Mgmt    Collabnet           4                         0
y    Agile/Proj. Mgmt    HP ALM              0                         4
y    Build               ANT                 3                         0
y    Build               MAVEN               3                         0
y    Build               MS build            0                         3

Answer 1

I think it might be "cleaner" to do a union all and group by:

select account, activity, tools_name, 
       sum(et_count) as et_count, sum(rt_count) as rt_count
from ((select et.account, et.activity, et.tools_name,
             et.count as et_count, 0 as rt_count
       from Existing_tools
      ) union all
      (select rt.account, rt.activity, rt.tools_name, 0, rt.count
       from Recommendation_Tool
      )
     ) er
group by account, activity, tools_name
order by Account, Activity, Tools_Name;

All the coalesce()s needed for the full outer join get tiresome.

Of course, you can also eliminate most of them using USING:

SELECT Account, Activity, Tools_Name, COALESCE(RT.Count, 0) as Rec_tool_Count, COALESCE(ET.Count, 0) as Existing_tool_count FROM Existing_tools ET FULL OUTER JOIN Recomendation_Tool RT USING (Account, Activity, Tools_Name) ORDER BY Account, Activity, Tools_Name

Answer 2

Seems like a full outer join and a few coalesces would work.

• Coalesce() returns the first non-null value in a series. so in the example coalesce(ET.Account, RT.Account) ET.Account value will return if not null; otherwise RT.Account will be returned even if null; thus one way or another we get the Account value or a NULL (but only if the account isn't populated in both tables.)
• FULL OUTER JOIN will join two data sets together based on keys and return all records from either source matching on values in the 'on' when it can. thus combining records from both data sets where appropriate.

This does assume that the account, activity and tool name denote unique record in each table (and that none of the values are null)

SELECT coalesce(ET.Account, RT.Account) as Account
     , coalesce(ET.Activity, RT.Activity) as Activity
     , coalesce(ET.Tools_name, RT.Tools_name) as Tools_name
     , coalesce(RT.Count,0) as Rec_tool_Count
     , coalesce(ET.Count,0) as Existing_tool_count
FROM Existing_tools ET
FULL OUTER JOIN Recomendation_Tool RT
  on ET.Account = RT.Account
 and ET.Activity = RT.Activity
 and ET.Tools_name = RT.Tools_Name
ORDER BY coalesce(ET.Account, RT.Account), coalesce(ET.Activity, RT.Activity)

Not sure what you mean by group by account activity... maybe order by? Group by is used with aggregation which there is none here...

or maybe you want to sum the counts in which case a group by again makes sense as perhaps you have multiple records in one or both of the tables for an account tool and activity.

SELECT coalesce(ET.Account, RT.Account) as Account
     , coalesce(ET.Activity, RT.Activity) as Activity
     , coalesce(ET.Tools_name, RT.Tools_name) as Tools_name
     , sum(coalesce(RT.Count,0)) as SUM_Rec_tool_Count
     , sum(coalesce(ET.Count,0)) as SUM_Existing_tool_count
FROM Existing_tools ET
FULL OUTER JOIN Recomendation_Tool RT
  on ET.Account = RT.Account
 and ET.Activity = RT.Activity
 and ET.Tools_name = RT.Tools_Name
GROUP BY coalesce(ET.Account, RT.Account) as Account
       , coalesce(ET.Activity, RT.Activity) as Activity
       , coalesce(ET.Tools_name, RT.Tools_name) as Tools_name
ORDER BY coalesce(ET.Account, RT.Account), coalesce(ET.Activity, RT.Activity)