|
|
Posted by --CELKO-- on 05/17/06 05:38
>> 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.
Navigation:
[Reply to this message]
|