Get all rows from one table with added boolean indicating if link exists to single row in another ta-CodePudding

I have three tables: USERS, ITEMS, and USER_ITEMS. USERS and ITEMS are both independent with primary keys USER_ID and ITEM_ID, USER_ITEMS is a linking table just using USER_ID and ITEM_ID as foreign keys.

I want to query and get every row/column in ITEMS, while also adding a temporary boolean column to the response indicating if a given ITEMS row has a link in USER_ITEMS to a specified USER_ID. i.e., get all the item info, and indicate if a given user has an item.

Is there an efficient way to do this with a single query? Specifically using MySQL.

CodePudding user response：

select
    i.*,
    case
        when exists(
            select 1 from user_items ui 
            where ui.item_id = i.item_id and ui.user_id = ?
        )
        then 1
        else 0
    end as user_has_item
from
    items i