Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

I Have an Inner Join Query that is Returning the Same Values Multiple Times

I Have an Inner Join Query that is Returning the Same Values Multiple Times. Ultimately, I would like to join eight tables, all on the common field Production Date, so this is my attempt to just Join two tables.

My SQL is shown below under code.

A snapshot of a portion of the query results is below as well. It appears the L1 Inspected is repeating, but the L2 and total seem to be O.K,

SELECT [Archive 15th St L1 FPY Weekly Trend].ProductionDate AS L1_ProdDate, Sum([Archive 15th St L1 FPY Weekly Trend].Inspected) AS L1_Inspected, [Archive 15th St L1 FPY Weekly Trend].ProductionDate AS L2_ProdDate, Sum([Archive 15th St L2 FPY Weekly Trend].INSPECTED) AS L2_Inspected, ([Archive 15th St L1 FPY Weekly Trend].Inspected+[Archive 15th St L2 FPY Weekly Trend].INSPECTED) AS SumofInspected
FROM [Archive 15th St L1 FPY Weekly Trend], [Archive 15th St L2 FPY Weekly Trend]
WHERE ((([Archive 15th St L1 FPY Weekly Trend].ProductionDate)=[Archive 15th St L1 FPY Weekly Trend].[ProductionDate]))
GROUP BY ([Archive 15th St L1 FPY Weekly Trend].Inspected+[Archive 15th St L2 FPY Weekly Trend].INSPECTED), [Archive 15th St L1 FPY Weekly Trend.ProductionDate]
ORDER BY [Archive 15th St L1 FPY Weekly Trend.ProductionDate];

Open in new window

Query-Results.jpg
0
Rex85
Asked:
Rex85
  • 3
  • 2
1 Solution
 
ralmadaCommented:
Can you please provide an example of the result you expect?
0
 
Rex85Author Commented:
Something like this. The value, 6584, for example, only occurs once in the table, but it is repeated several times in the query results.
example-Desired-Output.jpg
0
 
ralmadaCommented:
See the point is that you're joining both tables with the ProdDate only. So in your first example it seems that some L2 items have the same L2_ProdDate so when you join then in seems to create "duplicates for L1"
 
Now just by looking are your query, you are joining them incorrectly (you're joining by the same table)
WHERE ((([Archive 15th St L1 FPY Weekly Trend].ProductionDate)=[Archive 15th St L1 FPY Weekly Trend].[ProductionDate]))

Should be
WHERE ((([Archive 15th St L1 FPY Weekly Trend].ProductionDate)=[Archive 15th St L2 FPY Weekly Trend].[ProductionDate]))
 
0
 
Rex85Author Commented:
Thank you. Wow. That was a stupid, stupid copy/ paste error on my part. Thanks.
0
 
Rex85Author Commented:
Thank you very much. I really appreciate it.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now