Как вернуть результаты совпадающие в join и убрать все что не совпадают в on?

Question

[parfeniov]

Очень большая проблема которую не могу решить несколько дней.
У меня есть таблица table2 которая связана с table1. И у каждого пользователя есть свой id, в таблице table2 может быть только одна запись для определенного t1id с userid.
Вот запросы для наглядности:

CREATE TABLE table1 (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE table2 (id INT AUTO_INCREMENT PRIMARY KEY, t1id INT, userid INT, state BOOLEAN);
INSERT INTO table1 (name) VALUES ('Mike'), ('Brad'), ('Ivan'), ('ktoto');
INSERT INTO table2 (t1id, userid, state) VALUES (1, 1, TRUE), (2, 1, FALSE), (4, 1, FALSE);
SELECT t1.name FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.t1id AND t2.userid = 1 AND t2.state = TRUE;

Мне нужен такой запрос который выдаст строки из table1 даже если в таблице table2 нету строк совпадающих по условию после ON. И одновременно с этим, если нашлась таблица в table2 связанная с table1 и последующие условия истинные, то вернуть эту строку показав t1.name.
Я поправил вопрос
Команда выборки должна вернуть 2 строки, но возвращает 4

Comments

[Максим]

Очень странная постановка, т.к. в table1 нет id на которую ссылается вторая таблица. Т.о. не понятно сколько строк должно вернуть

[parfeniov]

Максим, тут t1id ссылается на id, строк может вернуть очень много, но по другому ниак

Answer 1

[parfeniov]

Я наконец смог решить чётенько.
Вот точный ответ:

SELECT t1.name 
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.t1id
WHERE (t2.t1id IS NULL)
OR (t2.userid, t2.state) = (1, TRUE);

Всем спасибо

Answer 2

[FanatPHP]

Есть два основных типа джойнов - inner и outer
inner, который обычно сокращается до просто join, находит только те записи, которые присутствуют в обеих таблицах.
outer, который обычно сокращается до left join, возвращает все записи из левой таблицы, а из правой - только те, которым есть соответствия

Answer 3

[Максим]

Очень странная постановка, но попробую заглянуть в "хрустальный шар"

select name
from (
SELECT t1.name, t2.userid, t2.state 
FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.t1id 
) X
where (userid = 1 or userid is null) AND (state = TRUE or state is null);