May 30, 2012 at 9:10 am
Hello - I'm wondering if anyone has some elegant way to handle this problem: I have participant/event data. I gather all the participants to any of a selected list of events, then left join to the event data. So, when a participant has a row for event 3 AND for event 4, I have two rows because the event 4 data is null when the event 3 data are present and vice versa. Aside from hideous nested case statements...does anyone have any good way to prevent the multiple row problem?
Thank you for your insight --
Donna
May 30, 2012 at 9:16 am
Based solely on your post, no idea. The problem here is that we can't see from here what you see there. You need to help us understand your problem, and to do that it would help if you would read the first article I reference below in my signature block and follow the instructions on what you need to post and how to do it. Be sure that the sample data you provide is just that, sample data that you make up to represent your problem. Also, based on that sample data, be sure to post what your expected results should look like.
If I had to make a shot in the dark, however, I'd say you should probably be using an inner join not an outer join. Again, not sure as I can't see what you see.
May 30, 2012 at 10:37 am
second vote for what Lynn said. If you provide some DDL and sample data, we can probably help.
I suspect that an inner join is needed as well. When you gather the participants, they won't disappear from event 3 just because they aren't in event 4. The outer join would be if you wanted to show all participants, even if they hadn't been a part of an event.
May 30, 2012 at 1:24 pm
It's not easy data to replicate on some fake way, and I thought of this as some technique-driven thing where someone could just recognize the situation amd say, "oh, I handle that this way," but I'll what I can come up with. Inner joins did not work.
May 30, 2012 at 1:51 pm
dbursey (5/30/2012)
Hello - I'm wondering if anyone has some elegant way to handle this problem: I have participant/event data. I gather all the participants to any of a selected list of events, then left join to the event data. So, when a participant has a row for event 3 AND for event 4, I have two rows because the event 4 data is null when the event 3 data are present and vice versa. Aside from hideous nested case statements...does anyone have any good way to prevent the multiple row problem?Thank you for your insight --
Donna
Yeah, you need to at least post your query and expected results.
Also, you need to explain better what you are trying to accomplish. In the above example, you state "when a participant has a row for event 3 AND for event 4" which indicates to me that you may be filtering on only 1 participant? If you are not filtering, there certainly will not be 1 row for every participant fill of nulls for event 4 unless you have done something we don't understand.
How come inner joins don't work? Telling us something doesn't work without telling us why will make it very hard to help you.
Jared
CE - Microsoft
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply