Table A
| Courseid | LanguageId | Score | ScoreText |
|---|---|---|---|
| 1001 | 7 | 1 | T |
| 1001 | 9 | 1 | D |
| 1001 | 1046 | 1 | D |
Table B
| Courseid | Topicid | LanguageId | Score | ScoreText |
|---|---|---|---|---|
| 1001 | 2001 | 9 | -1 | -- |
| 1001 | 2002 | 9 | -1 | -- |
| 1001 | 2003 | 7 | 1 | T |
| 1001 | 2003 | 9 | 1 | - |
| 1001 | 2004 | 9 | 2 | ++ |
| 1001 | 2005 | 9 | 2 | ++ |
SQL query to get below results.
If no records in table A matches with table B, then get rows with languageid = 9 from table B
Expected result
| CourseGuid | TopicGuid | A.LanguageId | B.LanguageID | A.Score | A.ScoreText | B.Score | B.ScoreText |
|---|---|---|---|---|---|---|---|
| 1001 | 2001 | 7 | 9 | 1 | T | -1 | -- |
| 1001 | 2002 | 7 | 9 | 1 | T | -1 | -- |
| 1001 | 2003 | 7 | 7 | 1 | T | 1 | T |
| 1001 | 2004 | 7 | 9 | 1 | T | 2 | ++ |
| 1001 | 2005 | 7 | 9 | 1 | T | 2 | ++ |
| 1001 | 2001 | 9 | 9 | 1 | D | -1 | -- |
| 1001 | 2002 | 9 | 9 | 1 | D | -1 | -- |
| 1001 | 2003 | 9 | 9 | 1 | D | 1 | - |
| 1001 | 2004 | 9 | 9 | 1 | D | 2 | ++ |
| 1001 | 2005 | 9 | 9 | 1 | D | 2 | ++ |
| 1001 | 2001 | 1046 | 9 | 1 | D | -1 | -- |
| 1001 | 2002 | 1046 | 9 | 1 | D | -1 | -- |
| 1001 | 2003 | 1046 | 9 | 1 | D | 1 | - |
| 1001 | 2004 | 1046 | 9 | 1 | D | 2 | ++ |
| 1001 | 2005 | 1046 | 9 | 1 | D | 2 | ++ |