I am trying to figure out a select query that would return data combined from two tables but can't seem to figure out the proper join query. For example:
Table-A
Col1 Col2 Col3 Col4
001 joe 322 234
002 joe 654 0.00
003 jon 895 34
004 jon 689 654
005 jim 892 567
006 jim 894 786
Table-B
Col1 Col2 Col3
005 joe 322
006 joe 654
007 joe 233
008 joe 659
009 sam 345
010 jim 344
I want to return all rows in Table-B that contain joe in Col2 - simple enough, in the above example that would result in the first 4 rows.
SELECT * FROM TABLE-B WHERE Col2 = 'joe'
005 joe 322
006 joe 654
007 joe 233
008 joe 659
Here is where I am stuck, I want to add Table-A Col4 to each row only where Table-A Col2 = joe AND Table-A Col3 = Table-B Col-3. Since there are only 2 matches this should add values to the first two records but not the last two:
005 joe 322 234
006 joe 654 0.00
007 joe 233
008 joe 659
Anyone know the proper query to accomplish this? Basically I want all four records with something tacked on the end of two of them.
