Sign in to follow this  
Thevenin

LEFT JOIN tables of vastly different row counts.

Recommended Posts

I have no idea how to explain what I'm doing, so I'm just going to type this as I would if I were explaining it to someone who had no idea what SQL was, and assume that that person could read my mind. Basically, I've created a forum. There is a stored procedure in my MSSQL database (called something like "GET_PAGE-N_TOPICS"). The output of this stored procedure is a table, where each row corresponds to all the data needed to describe a single topic (topic_title, topic_body, number_of_replies, topic_creator, topic_date). Thus, the entire table composes a single page of the forum. We good so far? Great. Now, this works, and the code is great. But specifications have changed, and instead of just getting the topic_creator number, I need to get everything about the topic_creator. His name, his age, and everything he has ever ate. His name and age is easy. I just do a LEFT JOIN, and thus, appended two more columns onto the table that gets returned from the procedure. The sum of everything he ate, though, is a table of itself. Which may have over 4000 rows. And that's the problem. And it gets even more complicated. 4000 rows is alot to transfer. If the topic_creator owns two topics on a page. I really only want to send the 4000 rows ONCE. Not twice. The only two solutions to this that comes to mind readily, is (1) a hackish/slashish CURSOR, SQL LOGIC (WHILE, IF), and multiple-recordset procedure, or (2) return the original table, process it in C#, and then request the topic_creator's information on an individually basis. Any ideas on a better approach?

Share this post


Link to post
Share on other sites
Can we get a version for those of us that are familiar with SQL? What sort of format do you want the subset list? What are the specific requirements?

My first inclination is that you don't need everything they ever ate but some sum/count/average. My second inclination is a temp table for distinct topic_creators.

Share this post


Link to post
Share on other sites
Quote:
Original post by Telastyn
My second inclination is a temp table for distinct topic_creators.


Ahh, that's right. Temporary table variables and DISTINCT() clause provided a way to avoid cursors and if/case stuff. I'm returning multiple recordsets, but the SQL is legible at least.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this