Wednesday, 11 September 2013

Joining 2 tables where table2 contains multiple rows for each table1 row

Joining 2 tables where table2 contains multiple rows for each table1 row

I'm either over-thinking this or my SQL skills are lacking. I need to add
two columns to a table from a second table, but the values are in separate
rows in the second table. What SQL would do this? Here's an example.
transnum is the common column between the tables.
TABLE1
------
transnum id
9287 006
5782 004
5819 001
TABLE2
------
transnum code value
9287 A 198.38
9287 B 928.57
9287 C 568.25 -- code C values not needed
5782 A 219.53
5782 B 591.11
5782 C 852.32
5819 A 109.92
5819 B 251.81
5819 C 295.12
DESIRED RESULT
--------------
transnum id valueA valueB
9287 006 198.38 928.57
5782 004 219.53 591.11
5819 001 109.92 251.81

No comments:

Post a Comment