CODEWITHSUNDEEP
mysqlsqlselectgroup-by
Peabrain
Peabrain

Reputation: 589

SQL query statement Self Join?

new to SQL.

I have the following set of data

A   X       Y   Z    
1   Wind    1   1
2   Wind    2   1
3   Hail    1   1
4   Flood   1   1
4   Rain    1   1
4   Fire    1   1

I would like to select all distinct 'A' fields where for all rows that contain A have flood and rain.

So in this example, the query would return only the number 4 since for the set of all rows that contain A = 4 we have Flood and Rain.

I need the values of A where for a given value 'a' in A, there exists rows with 'a' that must contain all of the following fields provided (in the example Flood and Rain).

Please let me know if you need further clarification.

Upvotes: 0

Views: 32

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

You Shooud use count(distinct X) group by A and having

count(distinct...) avoid situation where you have two time the same value for X 

    select A
    from my_table  
    WHERE x in ('Flood', 'Rain')
    group  A
    having count(distinct X) = 2

Upvotes: 0

GMB
GMB

Reputation: 222482

I need the values of A where for a given value 'a' in A, there exists rows with 'a' that must contain all of the following fields provided (in the example Flood and Rain).

You can use aggregation, and filter with a having clause:

select a
from mytable t
where x in ('Flood', 'Rain') -- either one or the other
having count(*) = 2          -- both match

If tuples (a, x) tuples are not unique, then you want having count(distinct x) = 2 instead.

Upvotes: 1

Related Questions