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:Table1ID, Facility, TestId1, TestId210, 1, 001, 00210, 2, 001, 010Table2TestId, Description001, pH002, Temperature010, Specific GravityFinished product:ID, Facility, TestId1, Description1, TestId2, Description210, 1, 001, pH, 002, Temperature10, 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] |
|
|
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 Description2FROM @Table1 AS BINNER JOIN @Table2 AS S1 ON B.TestID1 = S1.TestIDINNER JOIN @Table2 AS S2 ON B.TestID2 = S2.TestID |
|
|
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 |
|
|
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! |
|
|
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 BRefer unspammed for more examples and learnings |
|
|
|
|
|