SQL SELECT only field where it contains words

Question

I'm interested in doing something like in this question, where I only get the result, if the column actually contains all the words given to it.

For example, if I have two columns, one being a timestamp, the other being the weather, I want to get only the timestamps where the weather was cloudy, rainy, snowy, and not if it was only cloudy, rainy. An example of the above would be sort of like:

SELECT distinct timestamp FROM mytable
WHERE Weather LIKE 'snowy'
   OR Weather LIKE 'cloudy'
   OR Weather LIKE 'rainy'

Which would return something like:

2017-09-22 snowy
2017-09-22 snowy
2017-09-22 cloudy
2017-09-22 rainy
2017-09-22 cloudy

But not any results from 2017-09-21, because it might only have been snowy and rainy that day etc.

The example query I did above would return all timestamps, as long as the weather matched the criteria.

Tag your question with the database you are using. – Gordon Linoff Sep 22 '17 at 11:08 — Gordon Linoff, Sep 22 '17 at 11:08

score 7 · Accepted Answer · answered Sep 22 '17 at 11:07

You can use group by and having. I would recommend:

SELECT timestamp
FROM mytable
WHERE Weather IN ('snowy', 'cloudy', 'rainy')
GROUP BY timestamp
HAVING COUNT(DISTINCT Weather) = 3;  -- get all of them

If you have no duplicates for timestamp/Weather in the table, then use COUNT(*) = 3 instead of COUNT(DISTINCT).

score -1 · Answer 2 · answered Sep 22 '17 at 11:49

-1

You can use like bellow ,

SELECT timestamp FROM mytable WHERE Weather IN ('snowy', 'cloudy', 'rainy')

Output : example

1

2

3

Dummy table

1 snowy

2 cloudy

3 rainy

4 aaaa

5 bbbb

answered Sep 22 '17 at 11:49

Nani

113
3
3
12

This doesn't actually answer the question. – Peter Abolins Sep 22 '17 at 12:06

SQL SELECT only field where it contains words

2 Answers2