Help on join query - SQL Server Forums

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

Help on join query

Author

Topic

yanaho
Starting Member

20 Posts

Posted - 2011-03-02 : 12:45:52

Hello friends, looking for some help on a query, can't accomplish.

In table one, there is fields, along with 3 employeeid fields.
AuditID
ManagerID
AccountManagerID
CSBID

In Table two lies the employee table with id and name fields
EmpID
EmpName

I need to pull the auditID from table one, and employees names from table two, instead of the managerID, amID, and csbid from table one in a single row.

Can anyone help?

Thanks in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-02 : 12:53:15

SELECT AUDITID, EMPNAME
FROM Table1 JOIN Table2 ON Table1.AuditID = Table2.EmpId

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

yanaho
Starting Member

20 Posts

Posted - 2011-03-02 : 13:45:02

Hi thanks,
but this is not the solution I was looking for.
AuditID is not important.

ManagerID, AccountManagerID, and CSBID are all to have EmpName instead of ID.

Sorry for the confusion.

Thanks!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-02 : 13:52:01

Never mind, Just replace the AuditID,EmpName with required column names you have in both tables, you will get what you want

. If you need to show a column whose name is common in both tables then you will need to write that column with the alias of its table e.g. Table1.EmpName. By the way i would also recommend to have a look at the Select syntax in books online, so that if you need you can do such variation yourselves too

Cheers
MIK

yanaho
Starting Member

20 Posts

Posted - 2011-03-02 : 14:01:11

NOt sure if this is what you meant, but here is a solution which gives the correct results. Can anyone tell me if there is an easier way? Or if this is good?

SELECT a.AuditID, b.EmpName as PName,c.EmpName as AMName, d.EmpName as CSBName
FROM Audits a
LEFT JOIN STHUNT.dbo.sthunt_users b ON a.ProducerID = b.empid
LEFT JOIN STHUNT.dbo.sthunt_users c ON a.AccountManagerID = c.empid
LEFT JOIN STHUNT.dbo.sthunt_users d ON a.CSBID = d.empid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources