August 29, 2009 at 6:24 am
Hi have a number of views that collect data from a number of tables.
Small example, I need to get project data, but I also need to get the top 5 risk and issues for this selected project. This is all done with various views, using functions .. What i need to to combine all if this into one big results.
View1 (latest project report) output = Project Name, Project ID, Project Status
View 2(top 3 risk for project) output = Porrect
what I need : One view showing : ProjectName, ProjectID, Project Status, Risk1, risk 2, risk 3.
What SQL command can I use, or what is the best method .??
Thanks
August 29, 2009 at 6:58 am
albertvanwyk
Without additional information it will be difficult to assist you in any meaningful manner. For example:
a. How are risks rated, what makes one risk greater than another.
b. Foreign keys linking the various tables.
c. etc., etc.
Please read the link in my signature block to learn what information should be posted to assist those who desire to assist you.
August 29, 2009 at 11:03 am
There is no "SQL command". You code with views as if they were tables. So the tools you have to work with are the standard JOINS, UNIONS, temporary tables and table variables, common table expressions, subqueries, etc.
Be warned however, that when multiple views reference the same tables, the optimizer may not produce an optimal execution plan. Your best bet may be to deconstruct the views and develop your query or procedure from scratch. If necessary, store the results of each step in temporary tables and then join them at the end.
I have to agree that you really haven't supplied enough information for anyone to supply you with a more meaningful answer. Read the suggested article. Given sample schema and data, people can visualize and understand your problem, and offer tested examples of code. Basically you've just told us that you have some lumber, nails, brick, and paint... and that you want to build a house. You're asking what tool builds a house.
Take a little more time and give some detail to work with and a lot of very good people might volunteer to help you out. They will even share the tools in their toolkits.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 29, 2009 at 11:59 am
Hi
Maybe I missed something, but it seems like you already have the functions working inside View1 + View2 and what you need is just to combine those two sets into one...
Well, that's a simple JOIN... like:
SELECT
Project Name, Project ID, Project Status, Risk1, risk 2, risk 3
FROM
View1 INNER JOIN View2 ON
View1.ProjectID = View2.Project
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply