InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:45:53 GMT20http://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxYou can avoid 2 of the 3 table scans of Table1 with a brute force "uncrosstab" (unpivot) followed by a cross tab (pivot). I haven't tested it for performance but the execution plan looks pretty good.I converted the test data to a 2005 compatible format and wrote the code for 2005 and up just in-case folks with 2005 would want to do this. Since I'm doing all of this on a 2008 box, I can assure you it will also work in 2008.[code="sql"]--============================================================================================================-- Create and populate the test tables. This is NOT a part of the solution.-- I did, however,add the expected PK's which also create indexes.--============================================================================================================DECLARE @Table1 TABLE ( EID INT NOT NULL PRIMARY KEY CLUSTERED, EmployeeName VARCHAR(32) NOT NULL, Title VARCHAR(32) NOT NULL, Number CHAR(12) NOT NULL );DECLARE @Table2 TABLE ( Date DATETIME NOT NULL PRIMARY KEY CLUSTERED, AnalystEID INT NULL, JourneyEID INT NULL, AccountantEID INT NULL ); INSERT INTO @Table1 (EID, EmployeeName, Title, Number) SELECT 1,'John' ,'Analyst' ,'xxx-xxx-xxxx' UNION ALL SELECT 2,'Sam' ,'Journey Man','xxx-xxx-xxxx 'UNION ALL SELECT 3,'Sandra','Accountant' ,'xxx-xxx-xxxx'; INSERT INTO @Table2 (Date, AnalystEID, JourneyEID, AccountantEID) SELECT '2012-02-01', 1 , 2 , 3 UNION ALL SELECT '2012-02-03', NULL, 2 , NULL UNION ALL SELECT '2012-02-04', NULL, NULL, 3 UNION ALL SELECT '2012-02-05', 1 , 2 , 3;--============================================================================================================-- Solve the problem only 1 scan of each table.--============================================================================================================ SELECT Date = CONVERT(CHAR(10),t2.Date,101), AnalystEID = MAX(CASE WHEN ca.AttributeName = 'AnalystEID' THEN t1.EmployeeName ELSE '' END), JourneyEID = MAX(CASE WHEN ca.AttributeName = 'JourneyEID' THEN t1.EmployeeName ELSE '' END), AcccountantEID = MAX(CASE WHEN ca.AttributeName = 'AcccountantEID' THEN t1.EmployeeName ELSE '' END) FROM @Table2 t2 CROSS APPLY (SELECT 'AnalystEID' ,AnalystEID UNION ALL SELECT 'JourneyEID' ,JourneyEID UNION ALL SELECT 'AcccountantEID',AccountantEID) ca (AttributeName,AttributeValue) JOIN @Table1 t1 ON t1.EID = ca.AttributeValue GROUP BY t2.Date ORDER BY t2.Date;[/code]Fri, 01 Mar 2013 21:13:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxFWIW I was curious why my code didn't work so I snagged and slightly modified Lynn's code plugging in my select statement.[code="sql"]use tempdbgoCREATE TABLE Table_1 ( EID INT, Name VARCHAR(32), Title VARCHAR(32), Number CHAR(12));CREATE TABLE Table_2 ( Date DATE, Analyst_EID INT NULL, Journey_EID INT NULL, Accountant_EID INT NULL);INSERT INTO Table_1 ( EID, Name, Title, Number )VALUES ( 1, -- EID - int 'John', -- EmployeeName - varchar(32) 'Analyst', -- Title - varchar(32) 'xxx-xxx-xxxx' -- Number - char(12) ), ( 2, -- EID - int 'Sam', -- EmployeeName - varchar(32) 'Journey Man', -- Title - varchar(32) 'xxx-xxx-xxxx' -- Number - char(12) ), ( 3, -- EID - int 'Sandra', -- EmployeeName - varchar(32) 'Accountant', -- Title - varchar(32) 'xxx-xxx-xxxx' -- Number - char(12) );INSERT INTO Table_2 ( Date , Analyst_EID , Journey_EID , Accountant_EID )VALUES ( '2012-02-01' , -- EmpDate - date 1 , -- AnalystEID - int 2 , -- JourneyEID - int 3 -- AccountantEID - int ), ( '2012-02-03' , -- EmpDate - date NULL , -- AnalystEID - int 2 , -- JourneyEID - int NULL -- AccountantEID - int ), ( '2012-02-04' , -- EmpDate - date NULL , -- AnalystEID - int NULL , -- JourneyEID - int 3 -- AccountantEID - int ), ( '2012-02-05' , -- EmpDate - date 1 , -- AnalystEID - int 2 , -- JourneyEID - int 3 -- AccountantEID - int );--SELECT * FROM Table_1;--SELECT * FROM Table_2;Select Table_2.Date, MAX(t1analyst.Name ), MAX( t1j.Name ), MAX(t1acct.Name )From Table_2left join Table_1 t1analyst on Table_2.Analyst_EID = t1analyst.EIDleft join Table_1 t1j on Table_2.Journey_EID = t1j.EIDleft join Table_1 t1acct on Table_2.Accountant_EID = t1acct.EIDgroup by Table_2.DateOrder by Table_2.Date ASCDROP TABLE Table_1;DROP TABLE Table_2;[/code]Fri, 01 Mar 2013 13:22:46 GMTRP_DBAhttp://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxThank you.. Table_1 & Table_2 are already built in.It seems like, it the table is just not being recognized, which is odd.However, your code, I will also try and see how that works for me as well.Thanks again :-)Fri, 01 Mar 2013 12:57:04 GMTbeb9021http://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspx[quote][b]beb9021 (3/1/2013)[/b][hr]Hello RP_DBA.. I tried your method. However, in the message box I am getting this error message:Msg 208, Level 16, State 1, Line 1Invalid object name 'Table_1'.Is there a step that I am missing. I also faied to mention i am using SQL 2008 R2.Thanks [/quote]What you are missing is that he assumed that you already had tables named Table_1 and Table_2 based on your initial post. He did not build a test environment like I did with my code.Fri, 01 Mar 2013 12:53:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxHello RP_DBA.. I tried your method. However, in the message box I am getting this error message:Msg 208, Level 16, State 1, Line 1Invalid object name 'Table_1'.Is there a step that I am missing. I also faied to mention i am using SQL 2008 R2.Thanks Fri, 01 Mar 2013 12:39:15 GMTbeb9021http://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxThank you very much.. I am going to try bothFri, 01 Mar 2013 10:55:44 GMTbeb9021http://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxJust another solution:[code="sql"]DECLARE @Table1 TABLE ( EID INT, EmployeeName VARCHAR(32), Title VARCHAR(32), Number CHAR(12));DECLARE @Table2 TABLE ( EmpDate DATE, AnalystEID INT NULL, JourneyEID INT NULL, AccountantEID INT NULL);INSERT INTO @Table1 ( EID, EmployeeName, Title, Number )VALUES ( 1, -- EID - int 'John', -- EmployeeName - varchar(32) 'Analyst', -- Title - varchar(32) 'xxx-xxx-xxxx' -- Number - char(12) ), ( 2, -- EID - int 'Sam', -- EmployeeName - varchar(32) 'Journey Man', -- Title - varchar(32) 'xxx-xxx-xxxx' -- Number - char(12) ), ( 3, -- EID - int 'Sandra', -- EmployeeName - varchar(32) 'Accountant', -- Title - varchar(32) 'xxx-xxx-xxxx' -- Number - char(12) );INSERT INTO @Table2 ( EmpDate , AnalystEID , JourneyEID , AccountantEID )VALUES ( '2012-02-01' , -- EmpDate - date 1 , -- AnalystEID - int 2 , -- JourneyEID - int 3 -- AccountantEID - int ), ( '2012-02-03' , -- EmpDate - date NULL , -- AnalystEID - int 2 , -- JourneyEID - int NULL -- AccountantEID - int ), ( '2012-02-04' , -- EmpDate - date NULL , -- AnalystEID - int NULL , -- JourneyEID - int 3 -- AccountantEID - int ), ( '2012-02-05' , -- EmpDate - date 1 , -- AnalystEID - int 2 , -- JourneyEID - int 3 -- AccountantEID - int );SELECT * FROM @Table1;SELECT * FROM @Table2;SELECT *FROM @Table2 t2 OUTER APPLY (SELECT t1.EmployeeName FROM @Table1 t1 WHERE t1.EID = t2.AnalystEID)dt1(AnalystName) OUTER APPLY (SELECT t1.EmployeeName FROM @Table1 t1 WHERE t1.EID = t2.JourneyEID)dt2(JourneyName) OUTER APPLY (SELECT t1.EmployeeName FROM @Table1 t1 WHERE t1.EID = t2.AccountantEID)dt3(AccountantName);[/code]Fri, 01 Mar 2013 10:53:17 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspx[quote][b]beb9021 (3/1/2013)[/b][hr]I ran this query:Select Date, Analyst_EID, Journey_EID, Accountant_EIDFrom Table_2join Table_1on Table_2.Analyst_EID = Table_1.EIDand Table_2.Journey_EID = Table_1.EIDand Table_2.Accountant_EID = Table_1.EIDOrder by Date ASC[/quote]What you are asking for here is any record(s) from Table_2 where the Analyst_EID, Journey_EID and Accountant_EID are all the same. What you really want is to create a pivot or crosstab query. I'm sure there are more elegant solutions but something like this would probably work:[code="sql"]Select Table_2.Date, MAX(t1analyst.Name ), MAX( t1j.Name ), MAX(t1acct.Name )From Table_2left join Table_1 t1analyst on Table_2.Analyst_EID = t1analyst.EIDleft join Table_1 t1j on Table_2.Journey_EID = t1j.EIDleft join Table_1 t1acct on Table_2.Accountant_EID = t1acct.EIDgroup by Table_2.DateOrder by Table_2.Date ASC[/code]Fri, 01 Mar 2013 09:00:00 GMTRP_DBAhttp://www.sqlservercentral.com/Forums/Topic1425556-145-1.aspxGood Morning -Can someone please help me. I have been trying to figure this one out and I am having some difficulties.I am trying to join 2 tables, to where it would show one output. This is a sample:Table_1:EID Name Title Number1 John Analyst xxx-xxx-xxxx2 Sam Journey Man xxx-xxx-xxxx3 Sandra Accountant xxx-xxx-xxxxxTable_2Date Analyst_EID Journey_EID Accountant_EID2/1/2012 1 2 32/3/2012 Null 2 Null2/4/2012 Null Null 32/5/2012 1 2 3I ran this query:Select Date, Analyst_EID, Journey_EID, Accountant_EIDFrom Table_2join Table_1on Table_2.Analyst_EID = Table_1.EIDand Table_2.Journey_EID = Table_1.EIDand Table_2.Accountant_EID = Table_1.EIDOrder by Date ASCThe outcome I am looking for is this, once I have joined the tables2/1/2012 John Sam Sandra2/3/2012 Null Sam Null2/4/2012 Null Null Sandra2/5/2012 John Sam SandraCan someone please let me know some suggestions to assist me along the way.Thank youFri, 01 Mar 2013 08:46:04 GMTbeb9021