View from a merge of two tables

[Tom Cat]

Hi,

I hope this is the right place to ask this question. If it is not,
please let me know a more appropriate place.

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.
EnterpriseManag er is being less than helpful too.

Can anyone help?

Thanks in advance.

-Tom

[SQL]

Re: View from a merge of two tables

This should do it

CREATE VIEW AssignedTo
AS
SELECT EmployeeID as ID,FirstName + LastName AS LongName
FROM tblEmployees
UNION ALL
SELECT DeptID,LongName
FROM tlkpDept

Use Query Analyzer not Enterprise Manager I forgot if EM let's you use
UNION

Denis the SQL Menace

http://sqlservercode.blogspot.com/

[Tom Cat]

Re: View from a merge of two tables

That worked great! Never knew about this UNION ALL thing. Thanks!

SQL wrote:[color=blue]
> This should do it
>
> CREATE VIEW AssignedTo
> AS
> SELECT EmployeeID as ID,FirstName + LastName AS LongName
> FROM tblEmployees
> UNION ALL
> SELECT DeptID,LongName
> FROM tlkpDept
>
> Use Query Analyzer not Enterprise Manager I forgot if EM let's you use
> UNION
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/[/color]

[Alexander Kuznetsov]

Re: View from a merge of two tables

a minor correction: use FirstName + ' ' + LastName

[--CELKO--]

Re: View from a merge of two tables

>> 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(dep t_id),
..
);
[color=blue][color=green]
>> The application I'm doing will track the status of our customer requests <<[/color][/color]

Then we need a table for those trouble tickets:

CREATE TABLE CustomerRequest s
(ticket_nbr INTEGER NOT NULL PRIMARY KEY,
ticket_date DATETIME DEFAULT CURRENT_TIMESTA MP NOT NULL,
cust_id INTEGER NOT NULL
REFERENCES Customers(cust_ id),
dept_id CHAR(5) NOT NULL
REFERENCES Departments(dep t_id),
emp_id INTEGER - null means department level
REFERENCES Personnel(emp_i d)
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_nam e,
P.emp_id, P.last_name, P.first_name,
FROM (CustomerReques ts 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.