SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 JOIN Question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

LHendren
Starting Member

USA
16 Posts

Posted - 06/11/2013 :  15:20:40  Show Profile  Reply with Quote
Using a stored procedure, I want to join two tables with the following fields and data:

Table1
ID, Facility, TestId1, TestId2
10, 1, 001, 002
10, 2, 001, 010

Table2
TestId, Description
001, pH
002, Temperature
010, Specific Gravity

Finished product:
ID, Facility, TestId1, Description1, TestId2, Description2
10, 1, 001, pH, 002, Temperature
10, 2, 001, pH, 010, Specific Gravity

MuMu88
Aged Yak Warrior

547 Posts

Posted - 06/11/2013 :  15:49:00  Show Profile  Reply with Quote


DECLARE @TABLE1 TABLE (ID INT, Facility INT, TestId1 VARCHAR(10), TestID2 VARCHAR(10));

DECLARE @TABLE2 TABLE (TestId VARCHAR(10), Descriptions VARCHAR(20));

INSERT INTO @TABLE1 VALUES
(10, 1, '001', '002'),
(10, 2, '001', '010');

INSERT INTO @TABLE2 VALUES
('001', 'pH'),
('002', 'Temperature'),
('010', 'Specific Gravity');


SELECT  ID, Facility, TestId1, 
		MAX(CASE WHEN T.TestID1 = T2.TestID THEN  Descriptions END) AS Description1, 
		TestId2, 
		MAX(CASE WHEN T.TestID2 = T2.TestID THEN  Descriptions END) AS Descriptions2 
		FROM @Table1 T JOIN @Table2 T2 
	ON T.TestId1 = T2.TestID OR T.TestID2 = T2.TestID
	GROUP BY ID, Facility, TestId1, TestId2
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4354 Posts

Posted - 06/11/2013 :  17:00:38  Show Profile  Reply with Quote
Using MuMu88's data:
SELECT
	B.ID,
	B.Facility,
	B.TestId1,
	S1.Descriptions AS Description1,
	B.TestID2,
	S2.Descriptions AS Description2
FROM
	@Table1 AS B
INNER JOIN
	@Table2 AS S1
	ON B.TestID1 = S1.TestID
INNER JOIN
	@Table2 AS S2
	ON B.TestID2 = S2.TestID

Go to Top of Page

LHendren
Starting Member

USA
16 Posts

Posted - 06/11/2013 :  17:10:32  Show Profile  Reply with Quote
Lamprey, thank you so much! I am a little rusty with SQL statements and had forgotten the power of GROUP BY
Go to Top of Page

LHendren
Starting Member

USA
16 Posts

Posted - 06/11/2013 :  17:14:04  Show Profile  Reply with Quote
MuMu88 and Lamprey...thanks to both of you. Huge, prompt help and I appreciate it!
Go to Top of Page

Rajan Sahai
Starting Member

India
8 Posts

Posted - 06/12/2013 :  11:33:11  Show Profile  Reply with Quote
Using MuMu88's data:

SELECT
B.ID,
B.Facility,
B.TestId1,
(Select Descriptions from @Table2 where testid = B.TestId1) AS Description1,
B.TestID2,
(Select Descriptions from @Table2 where testid = B.TestId2) AS Description2,
FROM
@Table1 AS B

Refer unspammed for more examples and learnings
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000