December 8, 2020 at 3:21 am
I need to create a view from 13 tables with all the columns selected
Budget (REFERENCE_NUMBER column 2 column 3 column 4 column 5)
Category
Company
Contract
Division
Facility
ModelNo
Ownership
Status
MaintenanceType
WorkOrder
Address
Building
Each of these 13 tables consists of reference no. which another table named OBJECT (which consists of all the reference no.) can be used as the core linking table for all the reference numbers.
I am unclear as to use union in my statement or joins on tables on the reference ID, how do I need to write my code ?
December 8, 2020 at 3:36 am
What if you just create a new query (like CTRL-N) and then CTRL-Shift-Q to design in query designer... then just join the tables that way?
You don't mean that all 13 tables have the same structure do you?
December 8, 2020 at 5:39 am
Something like below is what you need to do. If the column names are the same, you will need to make sure each has a different name in the final view.
CREATE VIEW dbo.view_name
AS
SELECT
O.REFERENCE_NUMBER,
B.data_col1, B.data_col2 AS data_col2_B,
C.data_col3, C.data_col2 AS data_col2_C
/*, ...*/
FROM dbo.OBJECT O
LEFT OUTER JOIN Budget B ON B.REFERENCE_NUMBER = O.REFERENCE_NUMBER
LEFT OUTER JOIN dbo.Category C ON C.REFERENCE_NUMBER = O.REFERENCE_NUMBER
LEFT OUTER JOIN ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 9, 2020 at 12:51 am
Thanks ! all tables have different structures and when i use the query designer i get duplicate values cause i guess it uses cross joins where as it it should be inner joins.
December 9, 2020 at 2:13 pm
Well, you can have the query designer show you the actual query generated to see if it is using inner joins or cross joins. My guess is that it is using inner joins and that you are getting duplicate values because some tables may have a single row of data for a specific reference_number and others may have multiple values for the same reference_number.
Joining all the tables together in a single view may not be what you really need, but then we don't have enough information to provide a good answer to your question. What are you attempting to accomplish with this view?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply