438,419 Members | 1,584 Online Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

# Data modeling question

 P: n/a I'm facing the next problem: I have a table with two columns (among others) modeling category and subcategory data for each row. I need to summarize info on this two columns, but with the next specs: 1.- Some grouping is only on the category column. 2.- Some other grouping consider the two columns. The values for the two columns come from external source, i.e. I have no means to know the precise universe of data (I suppose soon or later we'll have a sufficient sample of data, but for now it's not the case). So, I would like to have a grouping table so it's not necessary to insert a row for every pair of category and subcategory (although it would be the best approach for the sake of design's simplicity). As I don't know every possible combination, I would prefer something like 'this category is a - no matter the subcategory', and 'this other category + subcategory is b'. Let's go with a sample: -------------------------------------------------------- Create Table B ( -- groupings ---- categ char(8), subcateg char(5), what_group char(10) ) -- All rows with 432 code are cat. A ---- Insert B ( '00000432', ' ', 'Category A' ) -- All rows with 636 code are cat. C except when subcat is 8552 (cat. B) ---- Insert B ( '00000636', '08552', 'Category B' ) Insert B ( '00000636', ' ', 'Category C' ) -- Some data ---- Create Table A ( -- data ---- categ char(8), subcateg char(5) ) Insert A ( '00000432', '01322' ) Insert A ( '00000432', '01222' ) Insert A ( '00000432', '01100' ) Insert A ( '00000432', ' ' ) Insert A ( '00000636', '08552' ) Insert A ( '00000636', '08552' ) Insert A ( '00000636', '01100' ) Insert A ( '00000636', ' ' ) Insert A ( '00000636', '01111' ) -- The query like: Select b.what_group, count(*) as cnt From a Left Join b On /* ? ? ? ? */ -- Should give --- what_group cnt -------------- ---------- Category A 4 Category B 2 Category C 3 ------------------------------------------------------------------- It would be easier knowing all the pairs categ - subcateg. If I don't know them, is a good idea to model the grouping table as I've done with rows in B? TIA, Diego Bcn, Spain Jul 23 '05 #1 