the beest the beest - 5 months ago 9
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

NOT EXISTS
. 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
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


and
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


What I want are the
follower
s of
followee
'5' who do not have an
association_record
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

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.

Comments