Create View multiple tables and different column names

Question

I have 2 tables in my database:

DetectorStatus

DetectorStatusID    DetectorID    DateTime    DetectorModeID    Status
      1                471          time            2            0.7

StationStatus

StationStatusID    DetectorID    DateTime    StationModeID    Status
      1                1541        time            2           0.74

I want to create a view that looks like this:

StationStatusID    DetectorStatusID    DetectorID    DateTime    StationModeID    DetectorModeID    Status
      NULL                 1               471         time          NULL                2            0.7
      1                    NULL            1541        time          2                   NULL         0.74

Now, when i create the view like this:

CREATE view statusoverview AS
    SELECT * FROM [GMS_MAN].[dbo].[DetectorStatus]
    UNION ALL
    SELECT * FROM [GMS_MAN].[dbo].[StationStatus]

I get all the results inside 1 table. However StationModeID is inside DetectorModeID etc.

How do i create a view that looks like the given example?

When i change the SELECT * FROM and add the columns + NULLS, It says: No column was specified for column 1 of viewname — Mitch
– Mitch, Commented Mar 6, 2017 at 8:30

Damien_The_Unbeliever · Accepted Answer · 2017-03-06 08:29:54Z

1

You need to ensure that both result sets contain all of the columns you want:

CREATE view statusoverview AS
SELECT null as StationStatusID,
       DetectorStatusID,
       DetectorID,
       DateTime,
       null as StationModeID,
       DetectorModeID,
       Status
FROM [GMS_MAN].[dbo].[DetectorStatus]
UNION ALL
SELECT StationStatusID,
       null,
       DetectorID,
       DateTime,
       StationModeID,
       null,
       Status
FROM [GMS_MAN].[dbo].[StationStatus]

Note that the column names are taken from the first query, so it needs to name all of the columns. The second can omit naming the columns it's supplying nulls for.

answered Mar 6, 2017 at 8:29

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitch Over a year ago

Thank you! It's giving me the exact result that i want. Also very clear and understandable written. Can accept answer in 5 minutes.

Whencesoever · Accepted Answer · 2017-03-06 08:30:17Z

1

SELECT null as StationStatusID,DetectorStatusID,DetectorID,DateTime,null StationModeID,DetectorModeID,Status FROM [GMS_MAN].[dbo].[DetectorStatus]
    UNION ALL
    SELECT StationStatusID, null, DetectorID,DateTime,StationModeID, null ,Status FROM [GMS_MAN].[dbo].[StationStatus]

I might have do some column misspellings, but it iwll be something like that

answered Mar 6, 2017 at 8:30

Whencesoever

2,30617 silver badges26 bronze badges

Comments

Norbert Forgacs · Accepted Answer · 2017-03-06 08:31:17Z

0

Well, you won't use SELECT *, you will specify which columns you want to have in your table:

CREATE view statusoverview AS
SELECT detector.*, station.StationStatusID, station.StationMode,station.Status 
FROM DetectorStatus detector
LEFT JOIN StationStatus station ON station.DetectorID=detector.DetectorID.

Whatever you choose, I think you shouldn't "SELECT *, but specify what you need"

answered Mar 6, 2017 at 8:31

Norbert Forgacs

5932 gold badges10 silver badges28 bronze badges

Collectives™ on Stack Overflow

Create View multiple tables and different column names

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related