MariaDB: Select the fields from one column in one table that are not in a subset of another column from another table

MariaDB: Select the fields from one column in one table that are not in a subset of another column from another table

업데이트: 다음을 사용하는 답변 제공 안 함NOT EXISTS. MariaDB에 따르면 "MariaDB의 EXIST 조건을 사용하는 SQL 문은 외부 쿼리 테이블의 모든 행에 대해 하위 쿼리가 재실행되므로 매우 비효율적입니다."이 쿼리는 많이 사용될 것이므로 효율적이어야 합니다.

테이블이 두개 있습니다.following:

CREATE TABLE `following` (
 `follower` int(1) unsigned NOT NULL,
 `followee` int(1) unsigned NOT NULL,
 PRIMARY KEY (`follower`,`followee`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

그리고.association_record:

CREATE TABLE `association_record` (
 `user_id` int(1) unsigned NOT NULL,
 `post_id` int(1) unsigned NOT NULL,
 `answer_id` int(1) unsigned NOT NULL,
 `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`user_id`,`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

제가 원하는 것은follower의followee'5' 가 없는association_record88'이라는 글과 함께.아래 SQL은 다른 게시물을 읽고 생각해낸 것이지만 원하는 결과를 얻지는 못합니다.

select f.follower
from following f
left outer join association_record a
on f.follower = a.user_id
where f.followee = 5
and a.post_id = 88
and a.user_id is null

select f.follower from following f, response_record r where f.follower = r.user_id and f.followee = 5 and r.post_id = 88 and r.user_id is null

try this

To make things easier, it is better to start step by step.

So, we need people who follows user '5'? Here is the query.

select follower from following where followee = 5;

Now we need to know wich users have a record on post '88'.

select user_id from association_record where post_id=88;

Now we need fixing both queries in some way. Let's do it:

select follower from following where followee = 5 and not exists (select user_id from association_record where post_id=88 and user_id=follower);

And that's it. Here you have your query explained.

Here was the query that did it:

SELECT f.follower
FROM following f
LEFT OUTER JOIN association_record a
ON f.follower = a.user_id
AND a.poll_id = 88
WHERE f.followee = 5
AND a.user_id is null

Forgot to post the solution to my question after I solved it, and now a month later, I end up having a similar problem but without any reference to the original solution; didn't need it anymore.

Almost had to resolve the whole issue again from scratch; which would have been tough being that I never understood how the solution worked. Luckily, MySQL workbench keeps a log of all the queries ran from it, and trying queries to answer this question was one of the few times I've used it.

Moral of the story, don't forget to post your solution; you might be doing it for yourself.

Method 2: Using join

SELECT follower
FROM following, association_record
WHERE follower=user_id
  AND followee = 5
  AND post_id=88
  AND user_id = null;

Some SQL engines use != and some use <>. Try both if you have trouble.

ReferenceURL : https://stackoverflow.com/questions/37084178/mariadb-select-the-fields-from-one-column-in-one-table-that-are-not-in-a-subset