I'm trying to select the ids that have indices "a" and "b" by joining 3 tables, then using WHERE to select the rows I need. The three tables have roughly the following schema: I joined the 3 tables on id. Table 3 has a one-to-many relationship, where 1 id corresponds to multiple words: After joining the tables, they would look

Use SQL to select one id that correspond to two indexing words in two rows

I’m trying to select the ids that have indices “a” and “b” by joining 3 tables, then using WHERE to select the rows I need. The three tables have roughly the following schema:

Table 1: id | name | description
Table 2: id | start_time | date
Table 3: id | word

I joined the 3 tables on id. Table 3 has a one-to-many relationship, where 1 id corresponds to multiple words:

id | word
 1 | apple
 1 | bee
 2 | abandon
 2 | apple
 2 | bee

After joining the tables, they would look like

id | name | description | start_time | date | word
 1 | 100  | abcd        | 1200       | M    | apple
 1 | 100  | abcd        | 1200       | M    | bee
 2 | 200  | efgh        | 2200       | T    | abandon
 ...

My goal would be to select the ids that correspond to both the word “apple” and “bee”, as well as having a specific start time and date. But I can’t use

SELECT id 
FROM table 
WHERE start_time = 1200 
  AND date = "M" 
  AND word = "apple" AND word = "bee"

Because a single row can’t hold both “apple” and “bee”. Can someone explain how to select the ids that correspond to words in two different rows? Thanks!

Answer

This answers the original version of the question.

Use aggregation and having:

SELECT id
FROM table
WHERE word IN ('apple', 'bee')
GROUP BY id
HAVING COUNT(*) = 2;

Note: This assumes that you have no duplicates. If you can have duplicates, use COUNT(DISTINCT word) = 2.

Here is a db<>fiddle.

Advertisement

Answer