SQL JOIN + SUBQUERY?
Hello, I am having problems figuring out how to return a "BossName" column derived from the "SubmitTo" field in this query. The BossName should be the LName + FName of the SubmitTo value (which is an EmpID)
SELECT [TID]
,[LogID]
,[AcctNum]
,[CHolderName]
,[CHolderFName]
,[StmtDate]
,[TranDate]
,[PostDate]
,[Merchant]
,[ACCT_UNIT]
,[TranAmt]
,[ExpID]
,[ActivityNum]
,[Purpose]
,[EntertainWho]
,[TravelTo]
,[ThirdPartyInfo]
,[ImportDate]
,[LastSaved]
,[WasSplit]
,[SplitFrom]
FROM [CorpCard_new].[dbo].[Tran
SELECT [LogID]
,[EmpID]
,[IsApproved]
,[IsEnteredGL]
,[IsSubmitted]
FROM [CorpCard_new].[dbo].[Logs
SELECT
a.*, b.StmtDate, b.CHolderName + ', ' + b.CHolderFName As CHolderName
FROM
[CorpCard].[dbo].[Logs] a
JOIN
[CorpCard].[dbo].[Trans] b
ON
a.LogID = b.LogID
JOIN
(SELECT e.SubmitTo, e.FName + ', ' + e.LName AS BossName FROM [Directory].[dbo].[_Employees] WHERE e.SubmitTo=e.EmpID) e
ON
e.EmpID = a.EmpID
WHERE
a.isEnteredGL <> 1
GROUP BY
a.LogID, a.EmpID,a.IsApproved,a.IsEnteredGL,a.IsSubmitted,b.CHolderName, b.CHolderFName, b.StmtDate, e.SubmitTo, e.FName, e.LName ORDER BY b.CHolderNameSELECT [TID]
,[LogID]
,[AcctNum]
,[CHolderName]
,[CHolderFName]
,[StmtDate]
,[TranDate]
,[PostDate]
,[Merchant]
,[ACCT_UNIT]
,[TranAmt]
,[ExpID]
,[ActivityNum]
,[Purpose]
,[EntertainWho]
,[TravelTo]
,[ThirdPartyInfo]
,[ImportDate]
,[LastSaved]
,[WasSplit]
,[SplitFrom]
FROM [CorpCard_new].[dbo].[Tran
SELECT [LogID]
,[EmpID]
,[IsApproved]
,[IsEnteredGL]
,[IsSubmitted]
FROM [CorpCard_new].[dbo].[Logs
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The only change I made was adding SELECT DISTINCT L.LogID, ...
JUST A NOTE OF WARNING:
"select distinct" is NOT a cure all and you may be slowing down your query unnecessarily. If you are joining multiple tables and then using select distinct then the problem is usually in the joins, not the select clause. You may also be performing calculations (such as concatenations) then just throwing away many of those rows = wasted effort.
http://weblogs.sqlteam.com/jeffs/archive/2006/03/14/9289.aspx
Some Simple SQL Rules to Live By
DISTINCT is usually bad
A good rule of thumb -- if you need a distinct list of values in a single column or two, DISTINCT is the way to go. But if your results "don't look right" or you see some duplicate rows and can't figure out why, do NOT just add DISTINCT to your SELECT to "fix it" !! Step back, look at your joins, and re-write your query properly. Even worse, I've seen people simply add DISTINCT to all their SELECT's right from the start, to preemptively "avoid duplicates". Not good.
ASKER
Your solution is indeed returning the BossName now. Thank you for the education regarding this. The only change I made was adding SELECT DISTINCT L.LogID, ...