March 1, 2013 at 8:46 am
Good 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 Number
1 John Analyst xxx-xxx-xxxx
2 Sam Journey Man xxx-xxx-xxxx
3 Sandra Accountant xxx-xxx-xxxxx
Table_2
Date Analyst_EID Journey_EID Accountant_EID
2/1/2012 1 2 3
2/3/2012 Null 2 Null
2/4/2012 Null Null 3
2/5/2012 1 2 3
I ran this query:
Select Date, Analyst_EID, Journey_EID, Accountant_EID
From Table_2
join Table_1
on Table_2.Analyst_EID = Table_1.EID
and Table_2.Journey_EID = Table_1.EID
and Table_2.Accountant_EID = Table_1.EID
Order by Date ASC
The outcome I am looking for is this, once I have joined the tables
2/1/2012 John Sam Sandra
2/3/2012 Null Sam Null
2/4/2012 Null Null Sandra
2/5/2012 John Sam Sandra
Can someone please let me know some suggestions to assist me along the way.
Thank you
March 1, 2013 at 9:00 am
beb9021 (3/1/2013)
I ran this query:Select Date, Analyst_EID, Journey_EID, Accountant_EID
From Table_2
join Table_1
on Table_2.Analyst_EID = Table_1.EID
and Table_2.Journey_EID = Table_1.EID
and Table_2.Accountant_EID = Table_1.EID
Order by Date ASC
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:
Select Table_2.Date, MAX(t1analyst.Name ), MAX( t1j.Name ), MAX(t1acct.Name )
From Table_2
left join Table_1 t1analyst on Table_2.Analyst_EID = t1analyst.EID
left join Table_1 t1j on Table_2.Journey_EID = t1j.EID
left join Table_1 t1acct on Table_2.Accountant_EID = t1acct.EID
group by Table_2.Date
Order by Table_2.Date ASC
_____________________________________________________________________
- Nate
March 1, 2013 at 10:53 am
Just another solution:
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);
March 1, 2013 at 10:55 am
Thank you very much.. I am going to try both
March 1, 2013 at 12:39 pm
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 1
Invalid object name 'Table_1'.
Is there a step that I am missing. I also faied to mention i am using SQL 2008 R2.
Thanks
March 1, 2013 at 12:53 pm
beb9021 (3/1/2013)
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
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.
March 1, 2013 at 12:57 pm
Thank 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 🙂
March 1, 2013 at 1:22 pm
FWIW I was curious why my code didn't work so I snagged and slightly modified Lynn's code plugging in my select statement.
use tempdb
go
CREATE 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_2
left join Table_1 t1analyst on Table_2.Analyst_EID = t1analyst.EID
left join Table_1 t1j on Table_2.Journey_EID = t1j.EID
left join Table_1 t1acct on Table_2.Accountant_EID = t1acct.EID
group by Table_2.Date
Order by Table_2.Date ASC
DROP TABLE Table_1;
DROP TABLE Table_2;
_____________________________________________________________________
- Nate
March 1, 2013 at 9:13 pm
You 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.
--============================================================================================================
-- 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
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply