-2
$\begingroup$

Assuming I have a schema depicted in the image here, showing relationships(specialization) between Staffs, Academic Staffs and Non Academic Staffs. Is it possible to have a query that retrieves the department and level if the staff is an academic staff or state and unit if it is a non academic staff based on type attribute in the staff entity?

$\endgroup$
  • 2
    $\begingroup$ This seems to be a programming question, so off-topic here. $\endgroup$ – Yuval Filmus Apr 26 '18 at 10:14
0
$\begingroup$

For MS-SQL (MySQL should be very similar):

http://sqlfiddle.com/#!18/4f530/1

Schema:

CREATE TABLE Staff( id int PRIMARY KEY NOT NULL, type varchar(45) ) CREATE TABLE Acad( id int PRIMARY KEY NOT NULL, dep varchar(45) ) CREATE TABLE Non( id int PRIMARY KEY NOT NULL, unit varchar(45) ) INSERT INTO Staff(id, type) Values (1, 'acad'), (2, 'non') INSERT INTO Acad Values (1, 'department') INSERT INTO Non Values (2, 'unit')

Query:

SELECT * FROM Staff S LEFT JOIN Acad A ON S.id = A.id AND s.type = 'acad' LEFT JOIN Non N ON S.id = N.id AND s.type = 'non'

I don't think you can get anything conceptually better without using T-SQL (loops and dynamic code generation).

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.