the beest the beest - 2 years ago 87
MySQL Question

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

Update: Do not provide an answer that uses

. According to MariaDB "SQL statements that use the EXISTS condition in MariaDB are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table." This query will be used a lot, so it needs to be efficient.

I have two tables

CREATE TABLE `following` (
`follower` int(1) unsigned NOT NULL,
`followee` int(1) unsigned NOT NULL,
PRIMARY KEY (`follower`,`followee`)


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`)

What I want are the
s of
'5' who do not have an
with post '88'. The below SQL is what I came up with from reading other posts, but it doesn't get me the desired results:

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download