Display multiple (two) tables in single view

Question

I have these two tables:

CREATE TABLE Student
(
    Id             uniqueidentifier PRIMARY KEY DEFAULT NEWID(), 
    UserId         uniqueidentifier REFERENCES [User] (Id), --associated user
    FirstName      nvarchar(50) NOT NULL,
    DisplayName    nvarchar(30),
    LastName       nvarchar(30) NOT NULL,
    Gender         int,
    BirthDate      date,
);

CREATE TABLE Teacher
(
    Id          uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(),
    FirstName   nvarchar(50),
    LastName    nvarchar(50),
    DisplayName nvarchar(50),
    Abbreviation    nvarchar(10)     NOT NULL UNIQUE,
    UserId      uniqueidentifier REFERENCES [User] (Id), --associated user
);

In order to ensure that a User can only be associated two one person, I would like to create a view Person that displays all the rows from Teacher and Student, with all their columns, and a bit column that indicates whether it is a student or teacher. I would then add a UNIQUE INDEX on Person that ensures uniqueness in its UserId column.

I tried many things, like joins, APPLY, UNION; but I can't get it to work.

This does not work because duplicate column names:

CREATE OR ALTER VIEW VI_Person 
AS
    SELECT * 
    FROM Student, Teacher

Does not work because tables are not identical:

CREATE OR ALTER VIEW VI_Person 
AS
    SELECT s.FirstName, s.UserId
    FROM Student s
    UNION ALL 
    SELECT * FROM Teacher

Only displays student columns:

CREATE OR ALTER VIEW VI_Person 
AS
    SELECT s.FirstName, s.UserId
    FROM Student s
    FULL JOIN  Teacher t ON t.FirstName = s.FirstName 
                         AND t.UserId = s.UserId

Displays FirstName, LastName, UserId. bit indicating type and other fields still missing

CREATE OR ALTER VIEW VI_Person 
AS
    SELECT s.FirstName, s.LastName, s.UserId
    FROM Student s
    UNION ALL
    SELECT t.FirstName, t.LastName, t.UserId
    FROM Teacher t

Include those attempts, explain why they didn't work. `UNION (ALL)` seems to be what you want. — Thom A, Jun 10 '22 at 12:38
@Larnu done. `UNION ALL` works for displaying same columns, however this does not allow me to display table-specific columns or the bit-indicator. — Davidbeh, Jun 10 '22 at 12:55
It would be much easier if you moved the persons information into the Users Table, not sure why you have the name's etc. in these tables. Then you can have some very simple tables, like TeacherId, UserId and StudentId, UserId. Then you can easily uses joins to the User table and union The Teachers and Students. Not sure what you are trying to do with adding an extra identifier here, I would think that you should focus on business logic to not allow someone to be a student and teacher. — SoundWaves, Jun 10 '22 at 12:56
*"other fields still missing"* of course they are; you didn't define them in the `SELECT`... If you have a `SELECT` with 3 columns in it, it'll return **three** columns, not five. You need to define five columns if you expect five columns. — Thom A, Jun 10 '22 at 12:57
@Larnu sorry, I thought it would be more complex; later i figured it out (see answer). — Davidbeh, Jun 10 '22 at 13:32
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**30 years!!** ago) and its use is discouraged — marc_s, Jun 10 '22 at 16:07
The best way to model this in a database is a polymorphic association, see https://stackoverflow.com/questions/7000283/what-is-the-best-way-to-implement-polymorphic-association-in-sql-server — Charlieface, Jun 10 '22 at 18:15
@Charlieface so you mean adding a table `Person` that is referenced by the `Teacher` and `Student` tables? Again there would be the problem that the same `Person` could be referenced by a `Teacher` and a `Student`. — Davidbeh, Jun 10 '22 at 19:20
Correct. The way to to do this is to use a `Type` column. The base `Person` table uses this plus its `ID` (auto-increment) column as the primary key. Then each of the sub-tables foreign-key's both of those columns together, but the `Type` column is a computed column with a fixed value. So you end up with `Person (ID int identity, Type int, PRIMARY KEY(Id, Type))` and `Teacher (ID int, Type AS 1, PRIMARY KEY (ID, Type), FOREIGN KEY (ID, Type) REFERENCES Person (Id, Type))` and so on for each of the sub-tables — Charlieface, Jun 12 '22 at 00:16

Davidbeh · Answer 1 · 2022-06-15T12:28:15.063

0

Figured it out:

CREATE OR ALTER VIEW VI_Person WITH SCHEMABINDING AS
SELECT s.FirstName, s.LastName, s.UserId, s.DisplayName, s.Gender, s.BirthDate, 0 IsTeacher
FROM dbo.Student s
UNION ALL
SELECT t.FirstName, t.LastName, t.UserId, t.DisplayName, NULL, NULL, 1
FROM dbo.Teacher t;

However it turns out that Indexes cannot be created on Views that use UNION, INTERSECT, or EXCEPT, so it is useless for me

edited Jun 15 '22 at 12:28

answered Jun 10 '22 at 13:25

Davidbeh

53
1
5

1

I don't think you can use an index on a view at all, but if you need it like that, then why not create one table to hold both entities ? It looks to me they have enough in common, you just need a field to determine if it is a student or teacher. And then your index is super easy – GuidoG Jun 10 '22 at 13:30
1

@GuidoG you can `INDEX` a `VIEW` (known as an [Indexed View](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16) in SQL Server), but they must be deterministic as well as many other [additional requirements](https://learn.microsoft.com/en-us/sql/relational-databases/views/create-indexed-views?view=sql-server-ver16#additional-requirements). – Thom A Jun 10 '22 at 13:38
@Larnu I did not expect that, I stand corrected. Even an unique index ? What happens then if the table definition changes of one of the tables used in the view ? – GuidoG Jun 10 '22 at 13:41
A `VIEW` *must* be schema bound to be indexed, @GuidoG . – Thom A Jun 10 '22 at 13:41
Do you really want 1 for IsTeacher when coming from the Student table? That is backwards of how a bit is typically used. – Sean Lange Jun 10 '22 at 13:54
@SeanLange oops – Davidbeh Jun 10 '22 at 19:09

Display multiple (two) tables in single view

1 Answers1