how to take multiple rows from a query and and analyze it for 1 row in new query

Question

I have developed a query for the problem below. However, it is not showing desired results. Anyone have ideas?

table 1

ID FRUIT 
1  APPLE
1  APPLE
1  MANGO

Table 2

country  id
USA       1
UK        2

If count of fruit name is greater than 1, i need "yes" for festival and "no" for zero count.

select country ,id 
CASE 
when table1.count > 1 and table1.fruit='APPLE' 
then 'Y'
else 'N'
END as apple_festival,
CASE 
when table1.count > 1 and table1.fruit='MANGO' 
then 'Y'
else 'N'
END as mango_festival,
CASE 
when table1.count > 1 and table1.fruit='BANANA' 
then 'Y'
else 'N'
END as Banana festival, JOIN (SELECT id,fruit,count from table1  group by id,fruit) table1 on table1.id=table2.id

I want results like this:

COUNTRY id apple_festival mango_festival Banana_festival
USA     1         Y            Y              N

However, I am getting this:

COUNTRY id apple_festival mango_festival Banana_festival
USA     1         Y            N              N
USA     1         N            Y              N

people can use this fiddle for helping me..

Answer 1

You can do conditional aggregation:

select
    t2.country,
    t2.id,
    case when max(case when t1.fruit = 'apple'  then 1 end) = 1 then 'Yes' else 'No' end apple_festival,
    case when max(case when t1.fruit = 'mango'  then 1 end) = 1 then 'Yes' else 'No' end mango_festival,
    case when max(case when t1.fruit = 'banana' then 1 end) = 1 then 'Yes' else 'No' end banana_festival
from table2 t2
inner join table1 t1 on t1.id = t2.id
group by t2.id, t2.country