Picky, picky, Lynn! You must learn to read the Matrix in it's original code, how else will you see the Agents? 😀

Good advice, Celko. I'm just hoping this isn't an architecture that he's inherited and can make some structural changes or additions.

Erin

With the example you show in the attachment I don't see how a single account can have multiple parents. If it's not possible for an account to have multiple parent...

Let me understand this.. you have a many-to-many relationship (parent-child and child-parent) and you'd like to pull a list of all parents and their children only, no grandchildren.

Question:

Do you have to have two tables? Is it possible for child accounts to have more than one parent?

Erin

Is this something like what you're looking for?

select getdate() as DATE_TIME

select DB_NAME() as DB_NAME

select SYSTEM_USER as Cur_USER

begin transaction

[insert statement]

commit

select @@ROWCOUNT as InsertedRows

go

Booya! Well done!

Erin

This link expands on what Lowell is trying to explain. You add a Unique Key from the Table Design Gui.

http://www.blackwasp.co.uk/SQLUnique.aspx

Hope that helps.

Erin

You might want to look at reversing the concept in your code and building the main data set to reflect tuples that contain higher than average Enrollment and using the...

In theory it seems to me you could simply add

AND st.dbid IS NOT NULL

to your where conditions and that would clear up the issue?

Erin

Easy enough to do in SSMS but I haven't had much exposure to SQL CMD and running scripted files. Does it have to be done in one or the other...

I'll offer a piece of advice, though, in relation to your code.

SELECT Count() AS totalCourses,

...

And then throw some cursor action on top of that...sweet RBAR! Or you could save yourself all the effort and just put your application on a x286.. lol

Erin

Could just write a UDF to take your datetime and reformat it. Returning a string, of course.

--So completely broke the discussion thread as I didn't see the page count. My apologies.

Don