CODEWITHSUNDEEP
sqljoin
user2100835
user2100835

Reputation: 1

How to join a one to many relationship

I am trying to join 3 tables ... months, monthly_results and issue_results. There is exactly one monthly_results record per month, but 0 to n issue_results per month. I can set up a Left Join to get Month_results when no Issue_results. My problem is when i have multiple issue_results in a month. i only want one occurrence of the month_results to appear in my result set ... otherwise we double count results in reporting monthly totals.

For example, here are tables

Months 1,2,3

Monthly Results (Month, Copies)
(1, 5000)
(2, 2500)
(3, 1000)

Issue Results (Issue, Month, Orders)
(1,1,750)
(2,1,500)
(3,3,250)

When i join the tables, what i would like to see is the following:

(Month, Issue, Copies, Orders)
1, 1, 5000, 750
1, 2, , 500
2, , 2500,
3, 3, 1000, 250

Using Left Outer Joins i can get the 2nd month (with no issue to populate correctly), but unfortunately, i get the following duplicate monthly results in the second month 1 ...

1, 1, 5000, 750
1, 2, 5000, 500
2, , 2500,
3, 3, 1000, 250

Any and all suggestions are greatly appreciated.

Upvotes: 0

Views: 116

Answers (2)

Leslie Sage
Leslie Sage

Reputation: 391

There is not enough information in the given tables to determine which issue in month 1 the 5000 copies are associate with. The result you're looking for shows the 5000 copies associated with issue 1 and not issue 2, but there is no information in the tables that makes that distinction. To clarify:

The rows of your table with information about month 1 are as follows:

Monthly Results:

    (Month, Copies)
    (1,5000)

Issue Results:

    (Issue, Month, Orders)
    (1,1,750)
    (2,1,500)

Looking at it this way, you can see from the Monthly Results table that the only piece of information associated with the 5000 copies is month 1. Therefore, when you join to Issue Results, both issues in month 1 are matched to 5000 copies.

To get the result you're looking for, either:

1) Your first table would need to include issue (Month, Issue, Copies)
2) Or you need to state how you choose which issue 'gets' the copies (in the case that there are >1 issues per month). sgeddes offered a good solution where the first issue of the month is the issue that gets the copies.

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

Depending on your RDBMS, there are different ways to achieve this.

Here is a MySQL solution:

SELECT M.Month, 
  I.Issue,
  @copies:=IF(@prevMonth=M.Month,NULL,M.Copies) copies,
  I.Orders,
  @prevMonth:=M.Month
FROM MonthResults M
  LEFT JOIN IssueResults I ON M.Month = I.Month
  JOIN (SELECT @copies:=0) t;

Sample Fiddle Demo

Here is a more generic approach which should work with most RDBMS:

SELECT M.Month, 
  I.Issue,
  T.Copies,
  I.Orders
FROM MonthResults M
  LEFT JOIN IssueResults I ON M.Month = I.Month
  LEFT JOIN (
      SELECT Min(I.Issue) minIssue,
        M.Month, M.Copies
      FROM MonthResults M LEFT JOIN IssueResults I ON M.Month=I.Month
      GROUP BY M.Month, M.Copies
) t ON M.Month = t.Month AND (I.Issue = t.minIssue OR I.Issue IS NULL)

And another Fiddle demo

Upvotes: 0

Related Questions