Внутреннее соединение MS Access 2007 на одной таблице

Question

У меня есть стол t1. У него есть столбцы [id] и [id2].

Select count(*) from t1 where id=1;

возвращает 31 189 записей

Select count(*) from t1 where id=2;

возвращает 31 173 записей

Я хочу знать записи, где id2 находится в id=1, но не в id=2.

Итак, я использую следующее:

Select * from t1 a left join t1 b on a.id2=b.id2
Where a.id=2 And b.id=1
And b.id2 Is Null;

Возвращает ноль записей. Используя внутреннее соединение, чтобы увидеть, сколько записей имеют id2, я делаю...

Select * from t1 a inner join t1 b on a.id2=b.id2
Where a.id=2 And b.id=1;

И это возвращает 31 060. Так где же дополнительные записи в моем первом запросе, которые не совпадают? Я уверен, что я должен упустить что-то очевидное.

Пример данных

id    id2
1     101
1     102
1     103
2     101
2     102

Мои ожидаемые результаты - найти запись с "103". 'id2' не предоставлен.

Спасибо за любую помощь. Джефф

Answer 1

Вы пытаетесь сделать то, что обычно называется exclude join, Это включает в себя выполнение LEFT JOIN между двумя таблицами, затем с помощью WHERE предложение только для выбора строк, в которых правая таблица пуста, т. е. не было записи для присоединения. Таким образом, вы выбираете все из левой таблицы, кроме того, что существует в правой таблице.

С этими данными это будет выглядеть примерно так:

SELECT 
  t1.id, 
  t1.id2 
FROM test_table t1 
LEFT JOIN 
 (SELECT 
   id, 
   id2 
 FROM test_table 
 WHERE id = 2) t2
ON t2.id2 = t1.id2
WHERE t1.id = 1 
  AND t2.id IS NULL --This is what makes the exclude join happen

А вот SQLFiddle, демонстрирующий это в MySQL 5.7 с предоставленными вами образцами данных.

Answer 2

Я думаю, что, возможно, Access изменяет левое соединение на внутреннее, когда вы добавляете предложение where для фильтрации строк (я знаю, что SQL Server делает это), но если вы выполняете фильтрацию в производных таблицах, это должно работать:

select 
    a.* 
from 
    (select * from t1 where id = 1) a 
left join  
    (select * from t1 where id = 2) b 
on a.id2 = b.id2 
where b.id2 is null