Site icon Pets Seek

Joined CTEs with a where clause returns too many rows [closed]

I am getting a very strange issue using SQL. I am running this in SQL Server Management Studio. I have built two CTEs trying to find dates where membership starts and membership ends, with the end goal of being able to show the date ranges where members are active. The code that builds the CTEs is fairly extensive and I am leaving it out to start, but if it turns out to be needed I can add it. starting_points and ending_points are the names of the CTEs.

I run this query

SELECT *
FROM starting_points starts
JOIN ending_points ends
    ON ends.MEMBER = starts.MEMBER
    AND ends.rowNum = starts.rowNum
WHERE ends.MEMBER = 1

This is the result

MEMBER begin_date rowNum MEMBER end_date rowNum
1 20201116 1 1 20201118 1
1 20201120 2 1 20210316 2
1 20210402 3 1 99991231 3

When I run this code

SELECT *
FROM starting_points starts
JOIN ending_points ends
    ON ends.MEMBER = starts.MEMBER 
    AND ends.rowNum = starts.rowNum
ORDER BY ends.MEMBER

I get this result

MEMBER begin_date rowNum MEMBER end_date rowNum
1 20201116 1 1 20201118 1
1 20201120 2 1 99991231 2
2 20201113 1 2 99991231 1
3 20211209 1 3 99991231 1

I am very confused as to why this is happening. As far as I know, the where clause should only filter down to return rows where the condition is met, but in this case it is adding a new row and changing the value in one of the columns in row 2. For clarity, the correct return is the one that uses the where clause and returns three rows for member #1.

As far as I can tell this is only happening on one member. I have spot checked about 20 other members and have not found this happening on any of them.

Thank you in advance for the help.

Exit mobile version