Two select statements querying the same column in a view returning different data

Question

I have a view that I will call 'ProductsView'. The column in this view that I have queried is called 'ProductName'.

Select ProductName 
from ProductsView
where ProductID = '12345678'

Result: 'Book'

Then I create a new query that inner joins two views (one of which is the aforementioned 'ProductsView'). Here we have another two views called 'TransactionsView' and 'ProductionView'.

Select
TV.Column
TV.Column
PRV.Column
PRV.Column
PV.ProductName
From TransactionView as TV
Inner Join ProductsView as PV
On TV.ID = PV.ID
Inner Join ProductionView as PRV
on TV.ID = PRV.ID
where PV.ProductID = '12345678'

Result (for ProductName column): '325 Pages'

As seen above I have included the ProductName column is included in this query as well so you would assume that one of the rows returned would have the value of 'Book' inside the ProductName column, however it doesn't even return the same type of data.

I'm at a complete loss of why this is happening. Could this be something to do with the underlying tables?

Thanks

Answer 1

Without seeing some sample data and both the results you get now and the expected results it's impossible to tell, we can't see your data....

Having said that, some basic trouble shooting might go like this.

First make sure that the columns you are joining are of the same datatype, you ProductID appears to be text (based on the fact that you quoted the value you are querying against).

Then, start with your ProductsView query

SELECT * 
    FROM ProductsView PV
    WHERE PV.ProductID = '12345678'

Make sure that returns what you expect

Then Join the TransactionView

SELECT * 
    FROM ProductsView PV
        JOIN TransactionView TV ON PV.ID = TV.ID
    WHERE PV.ProductID = '12345678'

Again, check if you get the expected results and if so, finally join the ProductionView

SELECT * 
    FROM ProductsView PV
        JOIN TransactionView TV ON PV.ID = TV.ID
        JOIN ProductionView PRV ON PRV.ID = TV.ID
    WHERE PV.ProductID = '12345678'

Once you see where the results change it will narrow down the issue. Having said all that as you are not left joining then its' hard to see how you can get differing values between your two queries as the column you are questioning comes from the same table as the filter in the WHERE clause. I would expect to seethe same value or no records (if one or more joins failed)