By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,415 Members | 1,568 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,415 IT Pros & Developers. It's quick & easy.

View from a merge of two tables

P: n/a
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.
EnterpriseManager is being less than helpful too.

Can anyone help?

Thanks in advance.

-Tom

May 15 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
SQL
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/

May 15 '06 #2

P: n/a
That worked great! Never knew about this UNION ALL thing. Thanks!

SQL wrote:
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/


May 15 '06 #3

P: n/a
a minor correction: use FirstName + ' ' + LastName

May 15 '06 #4

P: n/a
>> 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.

May 17 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.