SQL Server Self Join on View
Posted on 2014-02-26
Experts, I am attempting to execute a query that uses a self join on a view. The view is not an indexed view.
The query runs just fine. However it produces more rows than actually exist in the data.
from [dbo].[vwALL_SOURCES] a
--LEFT join [dbo].[vwALL_SOURCES] b
--ON a.resortid = b.resortid
where a.resortid = '296'
AND (a.points is not null)
AND (a.points > 0)
AND (a.price is not null)
AND (a.price > 0)
When the query is run as above with the join commented out it returns 13 rows - that's what's actually in the data.
However, when the join code is added to the query, it returns 2964 rows or 228 rows for each of the 13 rows in the data.
It makes no difference whether the query is run as LEFT JOIN, INNER JOIN, or RIGHT JOIN. The result set is the same.
What is likely to be causing this to happen?
Thanks in advance.