CODEWITHSUNDEEP
sql
Patrick
Patrick

Reputation: 291

combined WHERE clauses subqueries

New to SQL and having difficulty setting up a select statement.

I have the 2 following tables and I am trying to query all data that has the width and the contextual data that corresponds to that same observation_id. My current query produces the query table shown.

Current Query Code

SELECT value, trait_name, observation_id
FROM measurements AS m
INNER JOIN
traits AS t
ON m.trait_id = t.trait_id
WHERE trait_name = 'width' OR trait_class = 'contextual'

How do I only return the contextual data when it has the same observation_id as the trait_name I selected? The desired output are the rows in the query table colored in green.

enter image description here

Upvotes: 0

Views: 55

Answers (2)

Sylvain Roy
Sylvain Roy

Reputation: 26

The way I understant your question is you want to display the observations only for trait_name='width', but both for trait_name='width' or trait_class='contextual'. The subquery to get the observation is

SELECT observation_id 
FROM measurements m
INNER JOIN traits t
ON m.trait_id=t.trait_id
AND t.trait_name='width'

and then the full query would look like

SELECT value, trait_name, observation_id
FROM measurements AS m
INNER JOIN
traits AS t
ON m.trait_id = t.trait_id
WHERE (trait_name = 'width' OR trait_class = 'contextual')
AND observation_id IN
    (SELECT observation_id 
    FROM measurements m
    INNER JOIN traits t
    ON m.trait_id=t.trait_id
    AND t.trait_name='width')

Upvotes: 1

Luuk
Luuk

Reputation: 14899

SELECT value, trait_name, observation_id
FROM measurements AS m
INNER JOIN
traits AS t
ON m.trait_id = t.trait_id
WHERE ( trait_name = 'width' OR trait_class = 'contextual' )
 and observation_id in (select observation_id 
                          from measurements m1
                          inner join measurements m2 on m1.observation_id=m2.observation_id
                                                    and m2.trait_name='temperature'
                          where m1.trait_name='width')

The sub-query will select all observation_id that have a 'temperature' and a 'width'

Upvotes: 0

Related Questions