Fun with grouping and table variable populating

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Fun with grouping and table variable populating

Author

Topic

njonson406
Starting Member

1 Post

Posted - 2011-04-04 : 15:14:52

Hi,
I am working on a grouping task but just can't quite get my mind wrapped around it. Being somewhat of a newbie, I am still
experiencing that learning curve.

So I will layout my data fist then ask what I am looking for at the end.

The master lookup table.
Table M


mID        mTitle
100147     my title
100145     another title
100148     yet another
100149     well finally

A table holding a field (dRef) that will get populated.
Table B


bID   bTitle                dRef
1     display 1             NULL
2     display 2             NULL
3     display 3             NULL
4     display 4             NULL

A reference table for connecting tables M and B (M.mid = J.mID)
Table J


mID       bID
100147    1
100145    1
100148    1
100149    2
100147    2
100145    3
100149    4
100147    4

Now the meat of what I am looking for.
I need to query table J to build two new tables D and C followed by updating the dRef field of table B with its matching reference from the group combination, Table D - field dID (B.dref = D.dID).

If this is not enough, lastly, if table J has only ONE table B.bID reference (like row bID#4 in table B) then B.dRef gets populated with its one J.mID value (in this case '100145').

Table C is populated from table J based on every unique combination of mID limited to the same J.bID (where count > 1).
Table C will be the group data reference for Table D (D.dID = C.dID)

Table D will be a group reference table with an newly created identity seed "dID" for every new unique group (where count > 1) found per set in table J.

Final results for newly created temp variable tables...
Table D


dID      
200       
201

Table C


dID       mID   
200       100147
200       100145
200       100148
201       100149
201       100147

Final results for table B update...


bID   bTitle                dRef
1     display 1             200
2     display 2             201
3     display 3             100145
4     display 4             201

I am having a tough time even getting started with.
Any help would be greatly appreciated!

Thanks,
~Nathan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-04 : 20:10:58

I was with you until the part where you said " now the meat..." but then, you describe two tables D and C. What is the rule for populating table D? It does not seem to be related to anythin that you had described up until that point.

Can youbdescribe the rules for building tables D and C?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources