So I know we’ve all been there banging out heads against the wall trying to figure out why we are getting duplicate rows. I found myself doing the same thing this morning, and was starting to walk down the road of recreating the query in SQL (time suck) then I remembered a technique that I had used in the past to figure it out relatively quickly.
Simply add the SysRow ID of every table that you are using in your query. (Obviously this won’t work with grouped queries/subqueries). Once you add them in, run the query, then in compare the GUIDS between the duplicated rows and the one that don’t match, are the ones that are creating the duplicate rows.
Mine all matched, because my problem was actually in a subquery where the data was grouped, but this still was useful because now I knew that I didn’t have duplicating issues in main query, and I didn’t have to waste time verifying that.
Anyways, just thought I would share.