Clever Geek Handbook

Cast field for bit 0 if null and bit 1 if not

I am using this method to check if a row is someone else's entered into another table.

select
  personID,
  name,
  (
    /* CAST THIS PART TO BIT return 1 if field is found, 0 if not */
    select top 1 parents.personID
    from parents
    where parents.personID=persons.personID

  ) as inUse
from persons

Can it be done as I commented?

+3


source to share


2 answers


You can use an expression exists

and case

:

select personID
     , name
     , case when exists ( select *
                          from parents
                          where parents.personID=persons.personID) then 1 else 0 end as inUse
from persons


Or just do left join

:

select distinct pe.personID
     , pe.name
     , case when pa.personID is null then 0 else 1 end as inUse
from persons pe
left join parents pa on pa.personID = pe.personID
+5


source


I would use LEFT OUTER JOIN and check the result.

It will look something like this:



select
  personID,
  name,
  case when parents.personID is null then 0 else 1 end as 'HasAParent'
from persons
left outer join parents
on parents.personID=persons.personID

It also allows you to use other fields of the Parents table of your choice (for example, show the name of the parent if the parent language exists).

+4


source






More articles:


All Articles