SQL - One to Many join with left outer join

Question

SQL Server 2008 r2

I have 2 tables

Table 1

Col1 Col2 Col3 Col4
123   08   1    1
123   08   1    1
123   08   2    1

Table 2

Col1 Col2 Col3
123   08   6

Table 1 is the main column so most of the data comes from this table and it must be a LEFT OUTER JOIN on to Table2 because Table1 has values(col1, col2) that dont always return values in TABLE2 and should appear as NULLS.

My query is

SELECT
  a.Col2,
  SUM(a.Col3),
  SUM(a.Col4),
  SUM(b.Col3)
FROM
  Table 1 a
  LEFT OUTER JOIN Table2 b
    ON a.col1 = b.col1 AND a.col2 = b.col2
GROUP BY a.Col2

This would return 08, 4, 3, 18

I would want this to return 08, 4, 3, 6

Because Table 2 joins on 3 rows it then triples the SUM value for b.Col3

What would be the best solution?

FrankPl · Accepted Answer · 2014-11-13 20:50:47Z

1

If you do not want to sum across the records, you can add b.Col3 to the GROUP BY:

SELECT
  a.Col2,
  SUM(a.Col3),
  SUM(a.Col4),
  b.Col3
FROM
  Table 1 a
  LEFT OUTER JOIN Table2 b
    ON a.col1 = b.col1 AND a.col2 = b.col2
GROUP BY a.Col2, b.Col3

answered Nov 13, 2014 at 20:50

FrankPl

13.3k2 gold badges17 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DtotheG Over a year ago

This worked well as I created a view and summed the contents of Table2 so then joined on the new view and used the group by with Col3, thanks for your help

sqlhdv · Accepted Answer · 2014-11-13 20:49:10Z

0

Please try this. This will also take care if you have two records in table2 for same col1 and col2.

SELECT 
a.Col2,
SUM(a.Col3),
SUM(a.Col4),
MAX(b.Col3) FROM
Table 1 a
LEFT OUTER JOIN (select col1, col2, sum(col3) as col3 from Table2 group by col1, col2) b ON a.col1 = b.col1 AND a.col2 = b.col2
GROUP BY
a.Col2

answered Nov 13, 2014 at 20:49

sqlhdv

2541 silver badge7 bronze badges

Comments

John Bollinger · Accepted Answer · 2014-11-13 20:55:37Z

0

If the result you want in the fourth column is not a sum, then it's not clear what you do want. If you can rely on all rows of each group to have the same value in that column (which they will in your example, but might not with different data), and if that's the value you want, then you can use this:

SELECT
  a.Col2,
  SUM(a.Col3),
  SUM(a.Col4),
  MIN(b.Col3)
FROM
  Table 1 a
  LEFT OUTER JOIN Table2 b
    ON a.col1 = b.col1 AND a.col2 = b.col2
GROUP BY a.Col2

You could also use MAX() instead of MIN(), of course.

answered Nov 13, 2014 at 20:55

John Bollinger

191k11 gold badges103 silver badges206 bronze badges

Collectives™ on Stack Overflow

SQL - One to Many join with left outer join

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related