andrewbadera
Programmer
I have a client with a less than fully normalized data model. they have products with product codes, class, category, manufacturer and notes fields. Originally, I was told the product code was unique; I later discovered that they have products sharing product codes but existing as separate records with different class, category, manufacturer and/or notes field values.
so now when returning search results on no criteria, or on class, category and/or product code, there are of course multple displays of various codes; the "duplicate" codes with different manufacturer or notes values should be displayed, but only a single instance of product codes appearing with multiple classes or categories. a simple DISTINCT won't do the trick, because some product codes should appear more than once.
is there any way to solve the display issue on the query side of things? sub queries maybe?
table: tlbProduct
columns:
pk - intProductID
strProductCode
intCategoryID
intClassID
strManufacturer
strNotes
so now when returning search results on no criteria, or on class, category and/or product code, there are of course multple displays of various codes; the "duplicate" codes with different manufacturer or notes values should be displayed, but only a single instance of product codes appearing with multiple classes or categories. a simple DISTINCT won't do the trick, because some product codes should appear more than once.
is there any way to solve the display issue on the query side of things? sub queries maybe?
table: tlbProduct
columns:
pk - intProductID
strProductCode
intCategoryID
intClassID
strManufacturer
strNotes