У меня есть 2 таблицы ниже:
Table_1
[Group No] [Test No] [Description]
123 1 [First Test]
123 2 [Second Test]
123 3 [Third Test]
Table_2
[Sample No] [Test No] [Result Description]
ABC 1 [Some More Result]
ABC 3 [Some Result]
DEF 1 [A Result]
DEF 2 [Results More]
DEF 3 [Bad Results]
Здесь мой запрос:
SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123') AND (Table_2.[Sample No] = 'ABC')
djacobson query:
SELECT Table_1.[Group No], Table_1.[Test No], Table_1.Description, Table_2.[Result Description]
FROM Table_1
LEFT OUTER JOIN Table_2 ON Table_1.[Test No] = Table_2.[Test No]
WHERE (Table_1.[Group No] = '123')
AND (Table_2.[Sample No] IS NULL OR Table_2.[Sample No] = 'ABC')
Это возвращает:
[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 3 [Third Test] [Some Result]
Но я действительно хочу:
[Group No] [Test No] [Description] [Result Description]
123 1 [First Test] [Some More Result]
123 2 [Second Test] NULL
123 3 [Third Test] [Some Result]
Возможно ли это? Я хотел бы вернуть запись с помощью теста № 2. Однако как мне присоединиться к записи, которая не существует? Или это просто невозможно? Каковы альтернативы?