SQL query permutations using 3 tables without repetition

Question

SQL query permutations using 3 tables without repetition

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

itortu SSCommitted Points: 1914 More actions · Answer 1

Do you think that using dynamic sql the only way this can be achieved? I like the first code example that you gave me. If that can be used as the stored procedure, that would be great news.

I have a question, using the latest code example: id MN0163104 shows a purchase total of $852, but per table the purchase total is: $284. I do not think that a sum of the purchases needs to be done.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 2

itortu (9/8/2016)
Do you think that using dynamic sql the only way this can be achieved? I like the first code example that you gave me. If that can be used as the stored procedure, that would be great news.
I have a question, using the latest code example: id MN0163104 shows a purchase total of $852, but per table the purchase total is: $284. I do not think that a sum of the purchases needs to be done.

Not to be rude but this leaking of new requirements at random intervals is for the birds... Why would you want to combine the data from multiple tables if you don't want the total? If you're looking for the MAX value from the 3 tables, just use MAX instead of SUM. If you're not looking for that either, please take the time to organize your requirements in full and explain EXACTLY the behavior you're looking for.

As for it having to be dynamic... No... It doesn't have to be dynamic but it certainly simplifies things and makes for a far more efficient execution plan.

itortu SSCommitted Points: 1914 More actions · Answer 3

Hi Jason, I completely understand your point. I truly appreciate your help, it been life saving. I was given this task in the very last minute and there were no requirements. I am trying my best to gather as much as I can, but unfortunately for my case, the people that can offer these specifications are not very available.

I am going to organize what I have so far, and then make another post.

I needed the MAX value, yes, not the SUM of the combined totals.

Thank you again for all your help.

Jason A. Long SSC-Insane Points: 23786 More actions · Answer 4

No worries... Take the last query I supplied and replace SUM with MAX and see if that gets you the results you're looking for. If not, see if you can a complete set of requirements and the expected results based on the test data (I just used the top 20 from each tab of you spreadsheet).