CODEWITHSUNDEEP
sqlsql-servergroup-by
user4071723
user4071723

Reputation:

SQL Group by with return boolean for any

I'm trying to do a group by and return a boolean for whether the group by contains a value in the group.

I have two Tables Title Table and Items Table.

The Title.ID is a foreign key to my Items Table.

My Items Table has a multiple format codes and I need to Select A Boolean if a group contains a format code

The Sql Statement look like:

 Select t.ID, Any(i.Formatcode = 'DOD') as hasDODItem
 From Title t
 join Item i on i.TitleID = t.ID
 group by t.ID.

I'm looking for a function that would be like Any(i.Formatcode = 'DOD') as hasDODItem

Upvotes: 5

Views: 4007

Answers (3)

jarlh
jarlh

Reputation: 44766

Use EXISTS to find out if Formatcode = 'DOD' is there:

select t.ID, case when exists (select 1 from Item i
                               where i.Formatcode = 'DOD'
                               and i.TitleID = t.ID) then true else false end
from Title t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Use case:

 Select t.ID, (case when i.Formatcode = 'DOD' then 1 else 0 end) as hasDODItem
 From Title t join
      Item i
      on i.TitleID = t.ID
 group by t.ID

EDIT:

If you just want to know ids that have a particular item, then use exists rather than a join:

 Select t.ID,
        (case when exists (select 1
                           from item i
                           where i.TitleID = t.ID and i.Formatcode = 'DOD' 
                          )
              then 1 else 0 end) as hasDODItem
 From Title t ;

The join is not necessary. I thought you wanted it for some reason.

Upvotes: 2

roman
roman

Reputation: 117350

select t.ID, max(case when i.Formatcode = 'DOD' then 1 else 0) as hasDODItem
from Title as t
    inner join Item as i on i.TitleID = t.ID
group by t.ID

or you can do this with subquery and exists:

select
    t.ID,
    case
        when exists (
            select *
            from Item as i
            where i.TitleID = t.ID and i.Formatcode = 'DOD'
        ) then 1
        else 0
    end as hasDODItem
from Title as t

Upvotes: 6

Related Questions