MYSQL Select Statement with IN clause and multiple conditions
There might be a simple solution here, but it looks like I worked. I am trying to query a table based on an array of values in two columns. Here is the relevant table structure and data examples
comment table id, userId, articleId .... etc etc article table id, userId .... etc etc Data: UserIds = 3, 10. ArticleIds = 1, 2
Let's say I'm trying to find all the comments for a specific set of article IDs: 1,2
I can easily use this query
select * from comments WHERE articleId IN(1,2)
However, this is where it gets tricky. I have a query that runs before the comment request that identifies the corresponding article IDs. These identifiers are in an array. The array also contains the corresponding user IDs for each article.
Now I want to query in the comments table only for articles in array (1,2) AND only for comments made by the author (3, 10).
A simple query above will return all comments on articles 1 and 20. So, for example, I can't figure out where to add another conditional that talks about onyl comments for articles 1, 20 AND the corresponding userId, 3, 10.
Any help or suggestions would be much appreciated!
source to share
I think the easiest way is to write:
SELECT comments.* FROM articles JOIN comments ON articles.id = comments.articleId AND articles.userId = comments.userId WHERE articles.id IN (1, 2) ;
AND articles.userId = comments.userId
is what enforces your requirement of "only those comments made by the original author".
Alternatively, you can use the suggestion
SELECT * FROM comments WHERE articleId IN (1, 2) AND EXISTS ( SELECT 1 FROM articles WHERE id = comments.articleId AND userId = comments.userId ) ;
or a one-line subquery:
SELECT * FROM comments WHERE articleId IN (1, 2) AND userId = ( SELECT userId FROM articles WHERE id = comments.articleId ) ;
source to share