View from a merge of two tables

SQL Apprentice Question
I have two tables.


CREATE TABLE tblEmployees
(
EmployeeID int identity NOT NULL,
LastName varchar(50) NULL,
FirstName varchar(50) NULL,
);


CREATE TABLE tlkpDept
(
DeptID char(5) NULL,
Name char(10) NULL,
LongName char(50) NULL
);


Now I want to create a view called AssignedTo. [The application I'm
doing, will track the status of our customer requests. Each request
can be assigned to either an individual employee or an entire
department]


I want the view to have two columns, ID and LongName. ID should be
either the DeptID or the EmployeeID. The LongName column should be
either the LongName or the FirstName + LastName.


I'm not even sure how to begin to write such a complex SQL.
EnterpriseManager is being less than helpful too.


Celko Answers

>> I have two tables. <<


Actually, you have no tables; they lack a primary key. Did you really
hire people whose names you do not know and sequentially number them?
Do you have departments without names?

You are also not asking for a proper query - you are violating 1NF by
trying to force one column to hold two different domain values.
Formatting is done in the front end in a tiered architecture, a
principle more basic than SQL programming.


Can I assume that you assign personnel to departments? I also assume
that there tables for departments, customers, etc. in the schema.
Let's try this:


CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
last_name VARCHAR(50) NOT NULL,
first_name VARCHAR(50) NOT NULL,
dept_id CHAR(5) NOT NULL
REFERENCES Departments(dept_id),
..
);



>> The application I'm doing will track the status of our customer requests <<


Then we need a table for those trouble tickets:

CREATE TABLE CustomerRequests
(ticket_nbr INTEGER NOT NULL PRIMARY KEY,
ticket_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
cust_id INTEGER NOT NULL
REFERENCES Customers(cust_id),
dept_id CHAR(5) NOT NULL
REFERENCES Departments(dept_id),
emp_id INTEGER - null means department level
REFERENCES Personnel(emp_id)
ON UPDATE CASCADE
ON DELETE SET NULL,
request_status INTEGER NOT NULL,
..);


Notice that I kick the request to the department level with DRI
actions. The query would be something like this.


SELECT R.ticket_nbr, R.cust_id, R.dept_id,
D.dept_name, D.long_dept_name,
P.emp_id, P.last_name, P.first_name,
FROM (CustomerRequests AS R
INNER JOIN
Department AS D
ON D.dept_id = R.dept_id)
LEFT OUTER JOIN
Personnel AS P
ON P.emp_id = R.emp_id;


Then you do the formatting in the front end.