I have a table that looks a bit like this:

country | group | offer | type | list
--------|-------|-------|------|-----
UK      | A     | 1     | AA   | 1
UK      | A     | 2     | BB   | 1
UK      | A     | 3     | CC   | 2
IE      | A     | 4     | AA   | 2
IE      | A     | 5     | BB   | 2
IE      | A     | 6     | CC   | 1
UK      | B     | 7     | AA   | 3
UK      | B     | 8     | BB   | 4
UK      | B     | 9     | CC   | 4

I want to return all rows where the following is true:

• Type = CC
• List value is shared with a row of type AA or BB that has the same group and country

So for example, my desired result would look like this:

country | group | offer | type | list
--------|-------|-------|------|-----
UK      | B     | 9     | CC   | 4

This row would be returned because it's type is CC, and it has the same list number as other rows whose country is UK and group is B.

Does anyone know how this can be done please?

CodePudding user response：

Just use EXISTS with required conditions like this

SELECT 
    t.*
FROM t
WHERE t.type = 'CC' 
      AND EXISTS (
          SELECT 1
          FROM t tt
          WHERE tt.type IN ('AA', 'BB') 
                AND tt.list = t.list
                AND tt."group" = t."group"
                AND tt.country = t.country
          )

Output

fiddle

CodePudding user response：

Assuming your SQL Dialect's concatenation symbol is || you can use SQL like this:

Select * 
from mytable 
where type = 'CC' 
  and list||'-'||group||'-'||country in (select list||'-'||group||'-'||country 
                                         from mytable 
                                         where type in ('AA', 'BB'))

SQL Server Version:

Select * 
from t a
where type = 'CC' 
  and concat(list,'-',a."group",'-',country) in (select concat(list,'-',b."group",'-',country) 
                                                from t b 
                                                where type in ('AA', 'BB'))