Hello,

I am working on a query between multiple databases to be view on a web app.  We have multiple web apps and we are integrating some functionality between them all into one page. 

The data will be for a delegate someone sets for themselves (they can have more then one also).  I need a way to pull the data view it as follows.

User, UserNumber, DelegateName, ForApp, ForApp, ForApp, ForApp, ForApp, ForApp

I have had no problem pulling the delegates themselves and which apps they are a delegate for,  but not which person they are the delegate for.

SELECT DISTINCT 
                      dbo.Employee.Position_Num AS Delegate_Position, dbo.Employee.Name AS [Delegate Name], 
                      (CASE WHEN Hierarchy.dbo.Employee.Position_Num = CVI_Delegate.Delegate THEN 1 ELSE 0 END) AS For_CVI, 
                      (CASE WHEN Hierarchy.dbo.Employee.Position_Num = Travel_Delegate.Delegate THEN 1 ELSE 0 END) AS For_Travel, 
                      (CASE WHEN Hierarchy.dbo.Employee.Position_Num = Dept_Deposits_Delegate.Delegate THEN 1 ELSE 0 END) AS For_DeptDeposit, 
                      dbo.OKCorral_Delegate.For_UserRoles AS For_OkCorralUR, dbo.OKCorral_Delegate.For_FiscalApprover AS For_OkCorralFA, 
                      (CASE WHEN Hierarchy.dbo.Employee.Position_Num = Reqs_Delegate.Delegate THEN 1 ELSE 0 END) AS For_Reqs
FROM         dbo.Employee LEFT OUTER JOIN
                      Reqs.dbo.Delegate AS Reqs_Delegate ON dbo.Employee.Position_Num = Reqs_Delegate.Position LEFT OUTER JOIN
                      DeptDeposits.dbo.Delegate AS Dept_Deposits_Delegate ON dbo.Employee.Position_Num = Dept_Deposits_Delegate.Position LEFT OUTER JOIN
                      CVI.dbo.Delegate AS CVI_Delegate ON dbo.Employee.Position_Num = CVI_Delegate.Position LEFT OUTER JOIN
                      dbo.OKCorral_Delegate ON dbo.Employee.Position_Num = dbo.OKCorral_Delegate.Position LEFT OUTER JOIN
                      Travel.dbo.Delegate AS Travel_Delegate ON dbo.Employee.Position_Num = Travel_Delegate.Position

This query works fine.  The problem I run into is I have to use the same Employee table to get the EmployeeName and Number

Which will be used for both the person and the delegate.  Any ideas will be a great help.  It had been suggested I use a procedure to accomplish this, but I have no idea where to start that at.  I also have tried a nested sbu-query but since a person can have more then one delegate for an app,  this through errors.  Thanks.

You simply need to join with the Employee table twice under different aliases.

I suggest you to re-write your query using aliases for all your tables. Don't use 4 name conventions in any place of the query other than FROM clause - it is going to be deprecated and it is also making query harder to read and modify. I started to look into it to show the needed changes, but your query is hard to read, as I said.

Probably. I am not sure if it needs to be a RIGHT JOIN or not as I can not read such complex query unless you re-write it to make easier to read.

SELECT     Hierarchy_Employee.Position_Num, Hierarchy_Employee.Name, 
(CASE WHEN Hierarchy_Employee.Position_Num = CVI_Delegate.Delegate THEN 1 ELSE 0 END) AS For_CVI, 
(CASE WHEN Hierarchy_Employee.Position_Num = Travel_Delegate.Delegate THEN 1 ELSE 0 END) AS For_Travel,                      
(CASE WHEN Hierarchy_Employee.Position_Num = Dept_Deposits_Delegate.Delegate THEN 1 ELSE 0 END) AS For_DeptDeposit,                       
(CASE WHEN Hierarchy_Employee.Position_Num = Reqs_Delegate.Delegate THEN 1 ELSE 0 END) AS For_Reqs,                      
 dbo.OKCorral_Delegate.For_UserRoles AS For_OkCorralUR, dbo.OKCorral_Delegate.For_FiscalApprover AS For_OkCorralFA,                      
 Hierarchy_Employee2.Position_Num AS Dele_Pos_Num, Hierarchy_Employee2.Name AS Dele_Name

FROM         dbo.Employee AS Hierarchy_Employee LEFT OUTER JOIN
                      Reqs.dbo.Delegate AS Reqs_Delegate ON Hierarchy_Employee.Position_Num = Reqs_Delegate.Position LEFT OUTER JOIN
                      DeptDeposits.dbo.Delegate AS Dept_Deposits_Delegate ON Hierarchy_Employee.Position_Num = Dept_Deposits_Delegate.Position LEFT OUTER JOIN
                      CVI.dbo.Delegate AS CVI_Delegate ON Hierarchy_Employee.Position_Num = CVI_Delegate.Position LEFT OUTER JOIN
                      dbo.OKCorral_Delegate ON Hierarchy_Employee.Position_Num = dbo.OKCorral_Delegate.Position LEFT OUTER JOIN
                      Travel.dbo.Delegate AS Travel_Delegate ON Hierarchy_Employee.Position_Num = Travel_Delegate.Position 
		

It is better although I prefer shorter aliases (just few characters).

Anyway, what is the problem again - do you get it now? What exactly do you want to join?

You need to show us structures of the relevant tables (some tables in your query may be irrelevant to the problem), what do you currently get with your query and what do you want to get. If you can provide some input data as insert commands, that will be even better.

Ok,  here are the tables Travel Application Database/Delegate Table

INSERT INTO [Travel].[dbo].[Delegate]
           ([Position]
           ,[Delegate]
           ,[StartDate]
           ,[EndDate]
           ,[CreateDate]
           ,[EditDate])
     VALUES
           (<Position, char(6),>
           ,<Delegate, char(6),>
           ,<StartDate, datetime,>
           ,<EndDate, datetime,>
           ,<CreateDate, datetime,>
           ,<EditDate, datetime,>)
GO

Requistions Application /Table Delegates

INSERT INTO [Reqs].[dbo].[Delegate]
           ([Position]
           ,[Delegate]
           ,[StartDate]
           ,[EndDate]
           ,[CreateDate]
           ,[EditDate])
     VALUES
           (<Position, char(6),>
           ,<Delegate, char(6),>
           ,<StartDate, datetime,>
           ,<EndDate, datetime,>
           ,<CreateDate, datetime,>
           ,<EditDate, datetime,>)
GO

Heirarchy Database for Purchasing Application / Delegates Table

INSERT INTO [Hierarchy].[dbo].[OKCorral_Delegate]
           ([Dept_Campus]
           ,[For_DeptNum]
           ,[Position]
           ,[Delegate]
           ,[StartDate]
           ,[EndDate]
           ,[CreateDate]
           ,[EditDate]
           ,[For_FiscalApprover]
           ,[For_UserRoles])
     VALUES
           (<Dept_Campus, char(2),>
           ,<For_DeptNum, char(5),>
           ,<Position, char(6),>
           ,<Delegate, char(6),>
           ,<StartDate, datetime,>
           ,<EndDate, datetime,>
           ,<CreateDate, datetime,>
           ,<EditDate, datetime,>
           ,<For_FiscalApprover, bit,>
           ,<For_UserRoles, bit,>)
GO

Heirarchy Database / Employee Table

INSERT INTO [Hierarchy].[dbo].[Employee]
           ([Dept_Num]
           ,[Position_Num]
           ,[Email]
           ,[Name]
           ,[Campus]
           ,[CWID]
           ,[Student]
           ,[Employee]
           ,[OPID]
           ,[Gender]
           ,[Birth_Date])
     VALUES
           (<Dept_Num, char(5),>
           ,<Position_Num, char(6),>
           ,<Email, varchar(75),>
           ,<Name, varchar(150),>
           ,<Campus, char(2),>
           ,<CWID, varchar(16),>
           ,<Student, char(1),>
           ,<Employee, char(1),>
           ,<OPID, char(4),>
           ,<Gender, char(1),>
           ,<Birth_Date, char(8),>)
GO

The other two databases also have a delegate table similar to the ones above.  As you can see all information about an employee is linked by their position number in the Employee Table.

The Position Number is used in the Position field and Delegate field of all the Delegate tables.

I am needing to pull an employee (actually all of them) List them and then show delegates for them by which applications the delegate holds permissions too. Which is the purpose of the delegate.  So a delegate may only be a delegate for one application or multiple which is why I am pulling from multiple databases.

If you get your original select into a temp table and select from it, what do you get - can you show a few rows?

I think it's a bit too confusing for me and if you need again to join with many tables from many databases, this may be a complex thing. I originally thought it's a problem of just using the same table twice aliased differently. But now I am totally confused.

I've looked your query and your description for a while, but I'm still in the dark. Keep in mind that to you "delegate", "app" etc are well-defined  entities. But for us on the outside they are just words without meaning.

I reformatted your query so that I could read it. Like Naomi I like short aliaes, as long aliases tends to hide the forest with a lot of trees.

SELECT   HE.Position_Num, HE.Name,
         CASE WHEN HE.Position_Num = CVID.Delegate
              THEN 1
              ELSE 0
          END AS For_CVI,
          CASE WHEN HE.Position_Num = TD.Delegate
               THEN 1
               ELSE 0
          END AS For_Travel,
          CASE WHEN HE.Position_Num = DDD.Delegate
               THEN 1
               ELSE 0
          END AS For_DeptDeposit,
          CASE WHEN HE.Position_Num = RD.Delegate
               THEN 1
               ELSE 0
          END AS For_Reqs,
          OK.For_UserRoles AS For_OkCorralUR,
          OK.For_FiscalApprover AS For_OkCorralFA,
          Hierarchy_Employee2.Position_Num AS Dele_Pos_Num,
          Hierarchy_Employee2.Name AS Dele_Name
FROM      dbo.Employee AS HE
LEFT JOIN Reqs.dbo.Delegate AS RD ON HE.Position_Num = RD.Position
LEFT JOIN DeptDeposits.dbo.Delegate AS DDD ON HE.Position_Num = DDD.Position
LEFT JOIN CVI.dbo.Delegate AS CVID ON HE.Position_Num = CVID.Position
LEFT JOIN dbo.OKCorral_Delegate OK ON HE.Position_Num = OK.Position
LEFT JOIN Travel.dbo.Delegate AS TD ON HE.Position_Num = TD.Position

For the last two columns in the SLEECT list, I did not find a matching table, and I assume that these are the columns you need help with. But what I have absolute no clue is how you would arrive to this Hierarchy_Employee2. Let's say that for a certain row in dbo.Employee you get a hit in Reqs.dbo.Delegate. It appears that in this case, you want to display the position and name of another Employee. But which column in Reqs.dbo.Delegate would take you back to the Employee table?

Often when people post this type of questions, we recommend that you post:

1) CREATE TABLE statements for your tables, including key definitions.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) The version of SQL Server you are working with.

This makes it simple to copy and paste into a query window to develop a tested solution.

In your case, I would recommend that you post a simplified with a the Employee table and a single Delegate table, as it seems that this should be sufficient to solve the general problem.