sql - SQL: return true/false if a related record is presented

Question

I have two tables:

ASSIGNMENTS (ID)
ASSIGNMENT_REVIEWS (ID, ASSIGNMENT_ID)

As a result of selecting I'd like to retrieve a flag if a review is already presented for the assignment. How to do it in the best way?

标签： sqloracle

Answer 1

You are looking for the exists statement:

select 
    id,
    case when exists (
        select 1 from assignment_reviews where assignment_reviews.assignment_id = assignments.id
    ) then 1 else 0 end as hasReview
from
    assignments