Can I use OUTER JOIN on 2 columns at the same time?

I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.

I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.

I have following query which does not produce desired result

SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.

I'm pretty sure it's simple solution but can not figure out myself.

The query is correct ,it has to give the expected result.

Could you provide us with sample data?

|||

That's true it works with scenario I specified but I simplified my real life challenge. Table2 actually contains an extra column called Game of type int. It's nullable. I want the same thing I specified originally but only if Game=15, so my tables are

Table1

label_id athlete_id

1 1 2 1 3 1 1 2 2 2 3 2

Table2

label_id athlete_id data game

1 2 5 15 2 3 4 15 1 7 7 17

I want all rows from Table1 corresponding to label_id and athlete_id between tables for Game=15

Query below (

SELECT Table1.label_id, Table1.athlete_id, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)

where game=15

)

returns single row. How can I return all rows with null values where no matches are found and only for game_id

|||

What is your expected result?

I think that following is the expected results from the records you given

labelid athleteid data game

1 1 NULL NULL

2 1 NULL NULL

3 1 NULL NULL

1 2 5 15

2 2 NULL NULL

3 2 NULL NULL

The following is the query

Code Snippet

Select * from

(SELECT Table1.labelid, Table1.atheleteid, data, game FROM Table1

LEFT OUTER JOIN Table2 on (Table1.labelid = Table2.labelid AND Table1.atheleteid = Table2.atheleteid)) as TempTable

where Game=15 or Game is null

|||

Use Full Outer Join:

Code Snippet

SELECT

isnull(table1.label_id,table2.label_id),

isnull(table1.athlete_id,table2.athlete_id),

data,

game

FROM

table1

FULL OUTER JOIN table2 ON

(

table1.label_id = table2.label_id

and table1.athlete_id = table2.athlete_id

)

WHERE

game=15

/*

label_idathlete_iddatagame

-- -- -- --

12515

23415

*/

Move the Where clause to ON itself

Code Snippet

SELECT

Table1.label_id,

Table1.athlete_id,

data,

game

FROM

Table1 Table1

LEFT OUTER JOIN Table2 table2 ON

(

Table1.label_id = Table2.label_id

AND Table1.athlete_id = Table2.athlete_id

) and game=15

/*

label_idathlete_iddatagame

-- -- -- --

11NULLNULL

21NULLNULL

31NULLNULL

12515

22NULLNULL

32NULLNULL

*/