Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 JOIN Question

Author  Topic 

LHendren
Starting Member

17 Posts

Posted - 2013-06-11 : 15:20:40
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

549 Posts

Posted - 2013-06-11 : 15:49:00
[CODE]

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
[/CODE]
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-11 : 17:00:38
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

17 Posts

Posted - 2013-06-11 : 17:10:32
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

17 Posts

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

Rajan Sahai
Starting Member

8 Posts

Posted - 2013-06-12 : 11:33:11
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
   

- Advertisement -