John Ellis
asked on
T-SQL: Reasons for Why Joining to a View's Query Does Not Work
Hello:
This is a just a general "question".
I copied a view's query, surrounded it with parentheses, and typed "select * from" in front of it so that I could "query the query". If I'm not mistaken, doing so creates a "subquery".
Afterward, I continued with my query by joining other tables to this subquery.
I ran the revised query. But, it did not give me the results that I was looking for.
What are some general reasons as to why taking a view's query and turning it into a subquery joined to other tables does not produce the expected results?
Thanks!
John
This is a just a general "question".
I copied a view's query, surrounded it with parentheses, and typed "select * from" in front of it so that I could "query the query". If I'm not mistaken, doing so creates a "subquery".
Afterward, I continued with my query by joining other tables to this subquery.
I ran the revised query. But, it did not give me the results that I was looking for.
What are some general reasons as to why taking a view's query and turning it into a subquery joined to other tables does not produce the expected results?
Thanks!
John
Last Comment
Éric Moreau
we can join views but i suspect your syntax. You normally just replace a table name with the view name. Can you show your query?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
What you outlined will work, you just have to remember to give the query in brackets an alias name (eg as Qlemo did name it x).
The reasons about unexpected results in general are not caused by the step of replacing the view with its query.
The most natural use of a view to join it with further tables still is
The only reason to replace the viewname with the entire view query is, you want to edit the query itself, not only use it as a subquery as is, ie to do a related or similar query, to circumvent where conditions or to create another view not based on the old one.
Otherwise it's also a good advice to use the view, if your current query may also depend on the view definition in the future, even if the view changes. Like a parent class change in OOP. Copying code seldom is a best practice.
Bye, Olaf.
The reasons about unexpected results in general are not caused by the step of replacing the view with its query.
The most natural use of a view to join it with further tables still is
SELECT * FROM someview INNER JOIN sometable on someview.key=sometable.key ...
The only reason to replace the viewname with the entire view query is, you want to edit the query itself, not only use it as a subquery as is, ie to do a related or similar query, to circumvent where conditions or to create another view not based on the old one.
Otherwise it's also a good advice to use the view, if your current query may also depend on the view definition in the future, even if the view changes. Like a parent class change in OOP. Copying code seldom is a best practice.
Bye, Olaf.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
