Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Joins Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 27, 2013 10:28 AM
Points: 4, Visits: 15
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


Post #1425556
Posted Friday, March 1, 2013 9:00 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:25 AM
Points: 620, Visits: 865
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

@nate_hughes
Post #1425563
Posted Friday, March 1, 2013 10:53 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
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);





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1425643
Posted Friday, March 1, 2013 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 27, 2013 10:28 AM
Points: 4, Visits: 15
Thank you very much.. I am going to try both
Post #1425645
Posted Friday, March 1, 2013 12:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 27, 2013 10:28 AM
Points: 4, Visits: 15
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

Post #1425696
Posted Friday, March 1, 2013 12:53 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 PM
Points: 23,400, Visits: 32,256
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 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



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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1425701
Posted Friday, March 1, 2013 12:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 27, 2013 10:28 AM
Points: 4, Visits: 15
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
Post #1425703
Posted Friday, March 1, 2013 1:22 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, September 11, 2014 11:25 AM
Points: 620, Visits: 865
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

@nate_hughes
Post #1425712
Posted Friday, March 1, 2013 9:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:14 PM
Points: 37,107, Visits: 31,665
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425812
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse