odd DISTINCT requirements

[andrewbadera]

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

 

[tlbroadbent]

Sample data from the table and the expected result of a query would help me visualize the problem and possibly suggest solutions(s). Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[andrewbadera]

Terry-

As always, to the rescue

I may have avoided requirements to change the backend by displaying more information on the front-end. If the client is unhappy with my solution, I'll get back to you on this with sample data.

Thanks.