C# and SQL Server :: Find Most Common Via Junction Table
-
Hi, CP. I have a database hosted on an instance of SQL Server 2008 Express. I am using C# (.NET 4.0) and VS2010. I created a junction table in order to use a many-to-many relationship between two tables. I retrieve and manipulate data primarily using the main table but a part of the application requires me to find the most common value for a particular field, which is stored in a junction table. For example, I call a SELECT statement which retrieves the fields from the table [Repair Data]. The junction table, [Failure Codes], contains the fields [Serial Number] and Failure. In order to find the most common [Failure Codes].[Failure] value based on a [Repair Data] record ([Repair Data] includes a field [Serial Number], which is how the junction table knows which [Repair Data] record it belongs to) would I do something like this:
reader = SELECT * FROM [Repair Data]
foreach (Record r in reader)
{
// Do something with [Repair Data] fields/\* Find most common \[Failure Codes\].\[Failure\] in a separate query \*/}
or is there a simpler method for achieving these results? I think I explained it correctly. I am currently not at work and I try to forget work when I'm at home ;P. So I do not recall exactly what code I left off with. But I am quite positive I followed the "code" above. Thanks CP.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
-
Hi, CP. I have a database hosted on an instance of SQL Server 2008 Express. I am using C# (.NET 4.0) and VS2010. I created a junction table in order to use a many-to-many relationship between two tables. I retrieve and manipulate data primarily using the main table but a part of the application requires me to find the most common value for a particular field, which is stored in a junction table. For example, I call a SELECT statement which retrieves the fields from the table [Repair Data]. The junction table, [Failure Codes], contains the fields [Serial Number] and Failure. In order to find the most common [Failure Codes].[Failure] value based on a [Repair Data] record ([Repair Data] includes a field [Serial Number], which is how the junction table knows which [Repair Data] record it belongs to) would I do something like this:
reader = SELECT * FROM [Repair Data]
foreach (Record r in reader)
{
// Do something with [Repair Data] fields/\* Find most common \[Failure Codes\].\[Failure\] in a separate query \*/}
or is there a simpler method for achieving these results? I think I explained it correctly. I am currently not at work and I try to forget work when I'm at home ;P. So I do not recall exactly what code I left off with. But I am quite positive I followed the "code" above. Thanks CP.
djj55: Nice but may have a permission problem Pete O'Hanlon: He has my permission to run it.
I don't know if I understand correctly your issue. I understand the issue is, that you have tables: 1. [Repaired item] ([Serial Number], [Some data about item]) - stores information about items, that are repaired (car, or whatever). 2. [Failure Codes] ([Failure Code], [Information about failure]) - stores information about failures, that can be repaired. 3. [Repair Data] ([Serial Number], [Failure Code]) - stores connection between [Repaired item] and [Failure Codes]. With the structure above you want (for the given [Serial Number]) find the failure that occurs most often. If the above is correct, then I think you should do this in SQL using the following query:
SELECT
[Serial Number],
[Failure Code],
COUNT(*) AS [Counter]
FROM
[Failure Codes]
WHERE
[Serial Number] = @SerialNumber
GROUP BY
[Serial Number],
[Failure Code]
ORDER BY
[Counter] ASCAnd you can retrieve it easily from C# without any loops.
Don't forget to rate answer, that helped you. It will allow other people find their answers faster.