CODEWITHSUNDEEP
mysqlsqlrelational-database
Verdagon
Verdagon

Reputation: 2630

How to select from separate subclasses' tables?

I have a pretty simple database. The idea is that Student and Teacher both inherit from "Person" which is made of id and name.

create table Student (
    id int,
    name text,
    year int
);

create table Teacher (
    id int,
    name text,
    department varchar(15)
);

insert into Student (id, name, year) values
    (1, 'herper', 2007),
    (2, 'derpins', 2010);

insert into Teacher (id, name, department) values
    (3, 'skerp', 'csc');

I'm trying to make a select statement that will return this:

id  name       year   department
1   'herper'   2007   NULL
2   'derpins'  2010   NULL
3   'skerp'    NULL   'csc'

This is the closest I've gotten:

select *
from Student
union
select * from Teacher;

Any ideas? Thanks!

Upvotes: 0

Views: 160

Answers (1)

peterm
peterm

Reputation: 92815

SELECT id, name, year, NULL department
  FROM Student
 UNION
SELECT id, name, NULL, department 
  FROM Teacher;

Output:

| ID |    NAME |   YEAR | DEPARTMENT |
--------------------------------------
|  1 |  herper |   2007 |     (null) |
|  2 | derpins |   2010 |     (null) |
|  3 |   skerp | (null) |        csc |

SQLFiddle

Upvotes: 1

Related Questions