CODEWITHSUNDEEP
sqlsqlite
GeorgeCiesinski
GeorgeCiesinski

Reputation: 387

How to select a value from one table where two separate pairs of conditions are met in another table

I'm sorry in advance if this is a bad question. I'm new to SQL and haven't been able to find the answer myself.

I have two tables. The first table contains an id and unique words. The second table contains an id, property_name and property_value and can have multiple properties for each word.

word

+----+------+
| id | word |
+----+------+
|  1 | dog  |
|  2 | tree |
|  3 | dang |
|  4 | frog |
+----+------+

property

+----+---------------+----------------+
| id | property_name | property_value |
+----+---------------+----------------+
|  1 | letters       | 3              |
|  1 | first_letter  | d              |
|  2 | letters       | 4              |
|  2 | first_letter  | t              |
|  3 | letters       | 4              |
|  3 | first_letter  | d              |
|  4 | letters       | 4              |
|  4 | first_letter  | f              |
+----+---------------+----------------+

I want to select a word where two sets of conditions are met. Below is the pseudo-code of what I'm trying to achieve.

SELECT word
WHERE 
1) (property_name = 'letters' and property_value = '3')
2) (property_name = 'first_letter' and property_value = 'd')

First I searched google for examples of joining one table to another table where multiple properties can exist for each value in the first table. I had a lot of difficulty describing what I was searching for and wasn't able to find anything on google. I also searched stack overflow for similar questions, but the questions were only vaguely similar and the answers didn't work for me. All together, this is my third day in a row trying to find this solution.

Below are a few of my attempts:

I have tried nesting an and statement.

SELECT w.word
FROM word w
LEFT JOIN property p ON (w.id = p.id)
WHERE ((p.property_name = 'letters' and p.property_value = '3') and
(p.property_name = 'first_letter' and p.property_value = 'd'))
GROUP BY w.word

I also tried multiple left joins.

SELECT w.word
FROM word w
LEFT JOIN property p ON (w.id = p.id)
WHERE (p.property_name = 'letters' and p.property_value = '3')
LEFT JOIN property p ON (w.id = p.id)
WHERE (p.property_name = 'first_letter' and p.property_value = 'd')
GROUP BY w.word

The first attempt above returns a joined table where only the first condition is met. The second attempt returns a syntax error.

There were more failed attempts but I didn't include them for brevity.

For the pseudocode I wrote above, my desired result is:

dog

I just don't know how to write a query that does this.

Upvotes: 1

Views: 949

Answers (2)

peak
peak

Reputation: 116977

By using INTERSECT you can write a query that is efficient, intuitive and in fact very similar to what you first wrote:

SELECT word FROM words
WHERE id IN (
  SELECT id FROM properties WHERE property_name = 'letters' AND property_value = '3'
  INTERSECT
  SELECT id FROM properties WHERE property_name = 'first_letter' AND property_value = 'd'
);

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13026

first, don't use left join if you want to get matching property to your word second, no need to use group by, you are not using any aggregation function.

select w.word
from word w
inner join property p on w.id = p.id and p.property_name = 'letters' and p.property_value = '3'
inner join property p2 on w.id = p2.id  and p2.property_name = 'first_letter' and p2.property_value = 'd'

or you can use exists keyword

select w.word
from word w
where 
    exists (select 1 from property p where w.id = p.id 
        and p.property_name = 'letters' and p.property_value = '3')
    and
    exists (select 1 from property p where w.id = p.id 
        and p.property_name = 'first_letter' and p.property_value = 'd')

Upvotes: 2

Related Questions