Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Joins


Joins

Author
Message
beb9021
beb9021
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
RP_DBA
RP_DBA
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 1068
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24169 Visits: 37935
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);




Cool
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)
beb9021
beb9021
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 15
Thank you very much.. I am going to try both
beb9021
beb9021
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24169 Visits: 37935
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.

Cool
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)
beb9021
beb9021
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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 :-)
RP_DBA
RP_DBA
Right there with Babe
Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)Right there with Babe (767 reputation)

Group: General Forum Members
Points: 767 Visits: 1068
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44981 Visits: 39869
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search