CODEWITHSUNDEEP
sqlsqlite
Vassilis De
Vassilis De

Reputation: 373

sqlite extra column with 1 and 0 if record exists in related table or not

I am not so good at SQL, so I have the following tables

Stuff

Workers

and

Specialty

specialties

Let's say, the worker with name 'Bob' has two specialties. How could I get the specialty table with an extra column (let's say count) which has 1 if the record exists in Stuff and 0 otherwise.

I would like to ask if there is any way to cast a query that returns a result for Bob as shown below?

enter image description here

Any suggestions would be very helpful. Thank you in advance. (I am not sure about the title. Please do suggest if you have a better idea!)

Upvotes: 0

Views: 115

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

Use Left Outer join with Null check. Try this.

SELECT sp.specialitycode,
       sp.description,
       CASE
         WHEN st.specialitycode IS NULL THEN 0
         ELSE 1
       END AS count
FROM   speciality sp
       LEFT OUTER JOIN (SELECT specialitycode
                        FROM   stuff
                        WHERE  surname = 'Bob') st
                    ON sp.specialitycode = st.specialitycode

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would be inclined to do this with a case and exists:

select sp.*,
       (case when exists (select 1
                          from stuff s
                          where s.surname = 'Bob' and
                                s.speciality_code = sp.speciality_code
                         )
             then 1 else 0
        end) as BobHas
from specialty sp;

Upvotes: 2

Related Questions