Clever Geek Handbook

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!

+3


source to share


5 answers


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

The proposal 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 EXISTS

:



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
        )
;
+1


source


Have you tried just

select * from comments WHERE articleId IN(1,2) and authorId in (3,10)


If not, please update your question as to why this is the case.

+1


source


You can add as many operators IN

as you like:

select * from comments WHERE articleId IN(1,2) AND userId IN (3,10)
0


source


Can't you just make a query that runs the subquery first in the specified query:

SELECT * FROM `comments` WHERE `articleId` IN(1,2) AND `userId` IN (__subquery__)
0


source


- you can use subquery

select * from comments WHERE articleId IN(1,2)
and userId in ( select userId from article where id in (1,2) )
0


source






More articles:


All Articles