• Advertisement
Sign in to follow this  

LEFT JOIN tables of vastly different row counts.

This topic is 3606 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

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
Advertisement
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
Sign in to follow this  

  • Advertisement