Help with SQL Server query
- by Travis
Sorry* this is what I should have put
My query is creating duplicate entries for any record that has more than 1 instance (regardless of date)
<asp:SqlDataSource ID="EastMonthlyHealthDS" runat="server"
ConnectionString="<%$ ConnectionStrings:SNA_TRTTestConnectionString %>"
SelectCommand="SELECT [SNA_Parent_Accounts].[Company],
(SELECT [Monthly_HIP_Reports].[AccountHealth] from [Monthly_HIP_Reports] where ([Monthly_HIP_Reports].[YearMonth] = @ToDtRFC) AND ([SNA_Parent_Accounts].[CompID] = [Monthly_HIP_Reports].[CompID])) as [AccountHealth],
[SNA_Parent_Accounts].[CompID]
FROM [SNA_Parent_Accounts]
LEFT OUTER JOIN [Monthly_HIP_Reports] ON [Monthly_HIP_Reports].[CompID] = [SNA_Parent_Accounts].[CompID]
WHERE (([SNA_Parent_Accounts].[Classification] = 'Business') OR ([SNA_Parent_Accounts].[Classification] = 'Business Ihn')) AND ([SNA_Parent_Accounts].[Status] = 'active') AND ([SNA_Parent_Accounts].[Region] = 'east')
ORDER BY [SNA_Parent_Accounts].[Company]">
<SelectParameters>
<asp:ControlParameter ControlID="ddMonths" Name="ToDtRFC" PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Using SELECT DISTINCT appears to correct the problem, but I don't consider that a solution. There are no duplicate entries in the database. So it appears my query is superfically creating duplicates.
The query should grab a list of companies that meet the criteria in the where clause, but also grab the Health status for each company in that particular [YearMonth] if present which is what the subquery is for. If an entry for that YearMonth is not present, then leave the Health status blank.
but as stated earlier.. if you have an entry say for 2009-03 for CompID 2 and an entry for 2009-04 for CompID 2.. Doesn't matter what month you select it will list that company 2-3 times.
