CODEWITHSUNDEEP
sql-server
d00d
d00d

Reputation: 753

Exclude propery during join that is available in two tables?

I'd like to join four tables but unfortunately, two of the tables have a field called "Active" and "CreationDate" which I would like to exclude. An explicit list of every property to include is not feasible since it's 500 properties in total. Is there any command to exclude the Active property that are included in the VehicleFiles and Deliveries tables?

CREATE VIEW PDIReport AS
SELECT *
  FROM [LUX_WEB_SAM].[sam].[Deliveries] del  
  join [LUX_WEB_SAM].[sam].[VehicleFiles] vf  
     ON (vf.FK_Delivery = del.PK_Delivery)
     join [LUX_WEB_SAM].dbo.OrderNumbers ord
    ON (ord.FK_VehicleFile = vf.PK_VehicleFile)
    join [Autoline_Basis].[dbo].[Autoline_NEWST_Basis] newst
    ON (ord.CommissionNumber = newst.CommNumber)
    join [Autoline_Basis].[dbo].[Autoline_MK_Vehicle] vehicle
    ON (ord.CommissionNumber = vehicle.CommissionNumber)

Msg 4506, Level 16, State 1, Procedure PDIReport, Line 3 [Batch Start Line 0] Column names in each view or function must be unique. Column name 'CreationDate' in view or function 'PDIReport' is specified more than once

Upvotes: 0

Views: 34

Answers (4)

Wouter
Wouter

Reputation: 2976

You can use the system views INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES to get a list of all columns in your tables. You can use this to create the list of columns you want to select. Then copy-paste it in your view.

Upvotes: 1

Shubham Srivastava
Shubham Srivastava

Reputation: 1877

You can create a view, with list of columns you need and then use the view. This will reduce the burden and look a lot cleaner.

Upvotes: 1

d00d
d00d

Reputation: 753

Just used the Server Mangement Studios graphical capability to select all or deselect single attributes to create the view. Works perfect!

Upvotes: 0

Repr
Repr

Reputation: 201

There are 2 ways i can think of reducing the burden a bit. you can make the select like:

Select del.*, vf.<explicit fields>, ord.* etc

or use a dynamic statement to get the fields you want so you don't have to explicitly define them. it will just create the select row

Upvotes: 1

Related Questions