Search code examples
sqlpostgresqljoinsubqueryleft-join

Joining data from two postgresql tables with a flag if data exists in second table

I have two tables. The tools table stores a list of all available tools a user can enable on our website.

enter image description here

The user_tools table keeps track of the tools each user has installed. enter image description here

For example, if user with user_id 13 installs a tool with tool_id 'mailchimp', this will create a new row in the user_tools table containing user_id 13 and tool_id 'mailchimp'.

We have a page on our website that displays all tools with the currently installed tools in one section and the not-yet-installed tools in a second section.

enter image description here

I would like to write a single SQL query to populate this page. The query must fetch all tools and include a boolean column called installed that is true if the user has installed this tool. How can I do this?

I think I need an OUTER JOIN, and perhaps an EXISTS clause, but cannot figure it out.

Cheers.

Solution

  • You could use LEFT JOIN as follows:

    SELECT
    t.*,
    CASE WHEN u.tool_id IS NULL THEN 0 ELSE 1 END installed 
FROM 
    tools t
    LEFT JOIN user_tools u ON t.tool_id = t.tool_id AND u.user_id = ?

    This will return all tools, with the installed flag indicating whether each tool was already installed for the current user.

    The question mark should be replaced with the id of the user that is currently browsing your website.