Need to add another table in query...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need to add another table in query...

Author

Topic

parallon
Starting Member

25 Posts

Posted - 2006-06-19 : 14:24:11

Hello all. I have a query which is currently working great, although the customer requested that the report show FirstName and LastName instead of EmpID. Well, the problem I have is that I need the EmpID to link to the other tables in the query, and the FirstName and LastName are coming out of a different table. So, what I guess I'm getting at is that I need to somehow include this table in my script.

Here is the current SQL statement:

SELECT Schedule.Empid, Schedule.TransDate, MIN(Schedule.TransDate) AS MinTransDate, MAX(Schedule.TransDate) AS MaxTransDate, convert(varchar(200), Request) AS Request   
FROM Schedule INNER JOIN WorkOrder ON Schedule.WoNum = WorkOrder.WoNum    
WHERE (((Schedule.WoNum) In (SELECT WoNum
                             FROM WorkOrder
                             WHERE ProjectId = 'MMColParam')))
                             GROUP BY Schedule.Empid, convert(varchar(200), WorkOrder.Request), Schedule.TransDate
                             ORDER BY EmpID ASC, TransDate ASC

Somehow, I need to join Employee.FirstName and Employee.LastName to Schedule.EmpID. Any help would be greatly appreciated.

Thanks in advance,

Parallon

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 14:25:49

Doesn't the Employee table have an Empid column? If so, then just join Employee.Empid to Schedule.Empid.

Tara Kizer
aka tduggan

parallon
Starting Member

25 Posts

Posted - 2006-06-19 : 14:42:44

Wow, I appreciate your quick response, but I am fairly new with SQL and it has taken me some time to even put this mess together. I am just getting a little confused on how to squeeze it in the FROM statement. I am assuming I will need two sets of joins on this line (one from Employee to Schedule and one from Schedule to WorkOrder)???

Thanks,

Parallon

skativarapu
Starting Member

13 Posts

Posted - 2006-06-19 : 14:46:13

if u want to update the table u cazn do that by saying

update tablename
set yourcolumn = 'firstname' + 'lastname'
from tablex inner join tabley on tablex.code = tabley.code

befor doing this you have to create a column in the table for the name using modify table

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 14:49:25

I have removed a bunch of the query to show you how to do it. I also have added aliases and formatted the query. Aliases makes your code shorter plus easier to read. You no longer have to say Employee.Empid, you can say e.Empid instead. They don't have to be one letter, but mine are typically 1-3 letters.

SELECT ...
FROM Schedule s
INNER JOIN WorkOrder w
ON s.WoNum = w.WoNum
INNER JOIN Employee e
ON s.Empid = e.Empid
WHERE ...

Tara Kizer
aka tduggan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-19 : 14:51:34

quote:
Originally posted by skativarapu

if u want to update the table u cazn do that by saying

update tablename
set yourcolumn = 'firstname' + 'lastname'
from tablex inner join tabley on tablex.code = tabley.code

befor doing this you have to create a column in the table for the name using modify table

parallon,
I wouldn't recommend updating your data as mentioned above. Firstname and lastname should remain in 2 columns.

Tara Kizer
aka tduggan

parallon
Starting Member

25 Posts

Posted - 2006-06-19 : 14:52:23

Actually, all the information is already there. I am just creating a report, and need to tie into the Employee table to get the .FirstName and .LastName that correspond to the results of the subqueries.

Thanks,

Parallon

parallon
Starting Member

25 Posts

Posted - 2006-06-19 : 14:53:42

Thanks tkizer I will give that a try right now. :)

parallon
Starting Member

25 Posts

Posted - 2006-06-19 : 15:46:53

Thank you very much Tara Kizer. That worked great once I got my SELECT and GROUP BY cleaned up. I kept getting that Ambiguous Column Name error, but all is good now.

Thanks once again to all of you for your time and effort.

Parallon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources