I am trying to SELECT WHERE IN SELECT, what I am I doing wrong?

Question

SELECT * 
FROM `seriallog` WHERE `lastevent` IN
(SELECT GROUP_CONCAT(CONCAT(' \'', `eventid`, '\''))
        FROM `permissions` WHERE `permissions`.`userpkid` = 1)

Answer 1

You are likely trying to compare an int (lastevent) to a concatenated string (the result of your subquery).

It is hard to tell without more info, but this probably what you want to do:

select * 
from seriallog sl
inner join permissions p on sl.lastevent = p.eventid
where p.userpkid = 1

Answer 2

The way you've written it, the inner query will return a string like "1, 2, 3" so only rows in seriallog with a lastevent column of "1, 2, 3" exactly will match.

You're presumably interested in matching any row whose value is in a SET. That's what SQL is designed to do; you don't have to apply any special engineering. The following would work:

SELECT *
FROM seriallog WHERE lastevent IN
(SELECT eventid FROM permissions WHERE permissions.userpkid = 1)

However, it would be preferable to instead write:

SELECT *
FROM seriallog
WHERE EXISTS (SELECT 1 FROM permissions WHERE eventid = seriallog.lastevent AND permissions.userpkid = 1)

This allows MySQL to treat the query more like a JOIN, and presumably execute it more efficiently.

Answer 3

This is most optimized format for MySQL. In general MySQL does not love Sub-Queries.

SELECT * FROM seriallog sl join permissions p on sl.lastevent=p.eventid WHERE permissions.userpkid = 1