Design SQL Query for following case

Question

Consider tables

Table1
id, name
1   xyz
2   abc
3   pqr

Table2
id title
1  Mg1
2  Mg2
3  SG1

Table3
Tb1_id  tb2_id  count
1         1      3
1         2      3
1         3      4
2         2      1
3         2      2
3         3      2


I want to do query to give result like
id     title
1       MG1
2       MG2
3       Two or More Title

MG1 has higher preference if MG1 and count >= 1 then it is given as MG1 title , for others corresponding title is used and for count > 1 as two or more

Answer 1

I think this is what you are going for:

select t3.Tb1_id as id, 
    case
        when mg1cnt.count >= 1 then 'MG1'
        when cnt.count = 1 then upper(t2.title)
        else 'Two or More Titles'
    end as title
from (
    select Tb1_id, count(*) as count
    from Table3
    group by Tb1_id
) cnt
inner join (
    select Tb1_id, isnull(SUM(case when t2.title='mg1' then 1 end), 0) as count
    from Table3 t3
    inner join Table2 t2 on t3.tb2_id = t2.id
    group by Tb1_id
) as mg1cnt on cnt.Tb1_id = mg1cnt.Tb1_id
inner join Table3 t3 on cnt.Tb1_id = t3.Tb1_id
inner join Table2 t2 on t3.tb2_id = t2.id
group by t3.Tb1_id,
    case
        when mg1cnt.count >= 1 then 'MG1'
        when cnt.count = 1 then upper(t2.title)
        else 'Two or More Titles'
    end