mardi 14 avril 2020

Find parent id when all children share the same value

I have some data that looks a little like this

                 Table C
id | end_time
-------------
1    '2019-01-01'
2    '2020-01-01'    
3    '2019-07-01'    

                 Table F
id | parent_id
12 |    1
13 |    1

21 |    2
22 |    2

31 |    3
32 |    3
33 |    3
34 |    3



                  Table oui
rel_id | Product Version
1             '2'
12            '2'
13            '1'

2             '1'
21            '2'
22            '1'

3             '2'
31            '1'
32            '1'
33            '1'
34            '1'

Data relationship: c.id = f.parent_id

c.id or f.id = oui.rel_id

What I'm trying to find is where the rel_id for C in table oui is the parent's product version is 2, but ALL children are version 1.

I found a similar question over here: Find ID of parent where all children exactly match but couldn't quite adapt it to this use case.

Expected result:

c.id
----
 3

Reasoning: Both c.id 1/2 have children which have at least 1 item in product version 2.

Aucun commentaire:

Enregistrer un commentaire