MYSQL Select one column for two values

Question

I need to select a row from my mysql table.

The table has two rows with one equal value.

TABLE ----- articleId keywordId 

Now I need to select an article with the keyword Id = 1, as well as the keyword Id = 12.

Each keyword link has its own entry.

How can I make one select query to find out if there is an article that matches two keywords?

Answer 1

Try the following:

 SELECT * FROM tablename WHERE keywordId IN (1, 12) GROUP BY articleId HAVING COUNT(*) = 2; 

Check SQL FIDDLE DEMO

Answer 2

This is called the Relation Division . Here is one way to do this:

 SELECT * FROM tablename WHERE articleId IN ( SELECT articleId FROM tablename WHERE KeywordId IN (1, 2) GROUP BY articleId HAVING COUNT(KeywordId ) = 2 );; 

Answer 3

 SELECT * FROM `table_name` WHERE `keywordId` = '1' AND `keywordId` = '12' 

Answer 4

You can also use subqueries for each keyword and join them

 select k1.articleId from ( select articleId from TABLE where keywordId = 1 ) k1 inner join ( select articleId from TABLE where keywordId = 12 ) k2 on k1.articleId = k2.articleId 

Depending on the indexes and table size, this may be more efficient than Group By

Answer 5

 select ArticleId from Table where keywordId = 1 Intersect select ArticleId from Table where KeywordId = 12 

Answer 6

 SELECT * FROM table WHERE keywordId IN (1, 12);