Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables, with the following columns:

Table 1

  • ID
  • Component
  • Data1
  • Data2

Table 2

  • ID
  • Component
  • Data3
  • Data4

If I have rows in these tables, with items Component1, Component2, Component3, as entries in the component field for table 1, and Component3, Component4, and Component5 in the component field for table 2, I am looking to query such that I can get all results like:

Component1, data1, data2, data3, data4
Component2, data1, data2, data3, data4
Component3, data1, data2, data3, data4
Component4, data1, data2, data3, data4
Component5, data1, data2, data3, data4

Note that there are multiple entries of Component, and so I will need to group by the field to get aggregate summary data (using count, avg, etc. on the data fields). I am using SQL Server 2012 but need a solution that will work on 2005 ideally, a portable solution that works on other RDBMS would be preferable, but not necessary right now.

My question is two fold:

1) How do I perform this query? Inner, outer, and full joins seem useless, as they only let me retain Component from either one table or the other, not both. I'm unsure if a Cartesian product is the solution, I suspect it may be but I can't quite see how.

2) What is the most performant way of handling this, given that the number of entries in both tables will potentially be massive? Currently I have two SPs that get the summary data for the tables individually. As the SPs return only a handful of rows - maybe 20 to 30 in practise - because they summarise the data, is it more efficient/possible to perform the solution to part 1) above against the SPs rather than as a single query? An alternative I have is to simply merge the results of the SPs in code, I would rather do this on the database but efficiency will be quite important.

Any help appreciated- and I apologise if this question has been asked, it seems like it should be an obvious and common problem, but I couldn't find any help on Google et. al. though it could be that I was just using the wrong search terms!

share|improve this question
1  
Is Component1 present in both tables? If yes, how do you identify it in each table? If no, how do you get data3 and data4 for Component1? –  dezso Feb 25 '13 at 10:46
    
There's no guarantee that a component will be present in both tables and that's why I've been unable to simply use an outer join, which would suffice if one table was guaranteed to have all possible components. Effectively each table may have a subset of components, but I need to produce a summary table which lists all components showing summary data for each, filling in with 0 where there is no entry in table 1 or table 2 for that component. I noticed I mentioned primary key in the topic, that's wrong, component isn't a key - ID is. Edited to reflect this. –  Xefan Feb 25 '13 at 11:09
2  
A full outer join should retain rows from table 1 and table 2 which have no corresponding row in the other table. How is it not working for you? Are you joining on ID rather than Component? –  adhocgeek Feb 25 '13 at 11:30
    
Hi adhocgeek, I think you've probably found the solution, I wasn't aware of full outer join, only outer join and full join as separate entities. It appears this does something I was considering doing into a single operation - combines left and right joins into one which gets me part way towards a solution. I now need to be able to group by whichever table's entry for component isn't null. –  Xefan Feb 25 '13 at 11:41
add comment

1 Answer

up vote 4 down vote accepted

It's not entirely clear what you are after but I think a FULL outer join would help:

SELECT 
    COALESCE(a.Component, b.Component) AS Component
  , COALESCE(a.data1, 0) AS data1
  , COALESCE(a.data2, 0) AS data2
  , COALESCE(b.data3, 0) AS data3
  , COALESCE(b.data4, 0) AS data4
FROM 
    table1 AS a
  FULL JOIN
    table2 AS b
      ON b.Component = a.Component ;

If Component is not UNIQUE on one (or both) of the tables, then you could aggreagte and then join:

WITH a AS
  ( SELECT 
        Component
      , COUNT(*) AS cnt
      , SUM(data1) AS sum_data1
      , SUM(data2) AS sum_data2
      -- ...
      , AVG(data1) AS avg_data1
      -- ...
    GROUP BY
        Component
    FROM
        table1
  )
  , b AS
  ( SELECT 
        Component
      , COUNT(*) AS cnt
      , SUM(data3) AS sum_data3
      -- ...
      , AVG(data3) AS avg_data3
      -- ...
    GROUP BY
        Component
    FROM
        table2
  ) 
    SELECT 
        COALESCE(a.Component, b.Component) AS Component
      , COALESCE(a.sum_data1,0) AS sum_data1
      -- ...
      , COALESCE(b.sum_data3,0) AS sum_data3
      -- ...
    FROM 
        a FULL JOIN b
            ON b.Component = a.Component ;
share|improve this answer
    
This gets me almost there, I just need to be able to group by the resultant component (whichever isn't null!) –  Xefan Feb 25 '13 at 11:46
    
Nevermind, coalesce works in the group by clause too it seems. I think I can finish off what I need from here, thanks! –  Xefan Feb 25 '13 at 11:46
    
See my edited 2nd query. –  ypercube Feb 25 '13 at 11:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.