CODEWITHSUNDEEP
sqlite
Thibaud
Thibaud

Reputation: 617

SQLite select row with multiple condition from other table

I'm having probleme with a SQL request. I have two tables:

Id | Name
1  | Name_1
2  | Name_2

Id | _mainId | key  
1  |    1    | kw1  
2  |    1    | kw2  
3  |    1    | kw3  
4  |    2    | kw2  
5  |    2    | kw4

I would like a request which return the Id and Name of the mane table with all the keywords selected

something like this :

SELECT DISTINCT(t1.Id), t1.Name 
FROM       main t1 
INNER JOIN keywords t2 ON t2._Main = t1.Id 
WHERE      t2.keyword = 'kw2' AND  t2.keyword = 'kw4';

Upvotes: 0

Views: 926

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

In the query below, the subquery aliased as t2 identifies all IDs having both the keywords 'kw2' and 'kw4'. I then join the main table to this subquery to bring in the name information for those matching IDs.

SELECT t1.Id, t1.Name
FROM main t1
INNER JOIN
(
    SELECT _mainId
    FROM keywords
    WHERE keyword IN ('kw2', 'kw4')
    GROUP BY _mainId
    HAVING COUNT(DISTINCT keyword) = 2
) t2
    ON t1.Id = t2._mainId

Upvotes: 1

Sebastian
Sebastian

Reputation: 506

Try this:

SELECT DISTINCT
  t1.Id, t1.Name
FROM
  main t1
  INNER JOIN keywords t2 ON t2._mainId=t1.Id
WHERE
  t2.key IN ('kw2', 'kw4');

Upvotes: 0

Related Questions