SQL statement check if user is allowed

Question

I have the following three tables:

templates <-----> templatesubscription <-----> user

table template:
id | name

table user
id | name | ...

table templatesubscriptions
id | template_id | user_id

• Each user can have n template subscriptions.
• Each template subscription can have 1 user.
• Each template has n template subscriptions.
• Each template subscription has 1 template.

Now I want to check if the currently logged in user (User ID = 10) can edit the template with the ID = 0. Only if the user has an template subscription for this template he can edit it.

Now I could make 2 queries. First I will get the template subscription of the current user with the template id:

SELECT * FROM templatesubscription WHERE user_id = 10 AND template_id = 0

Now I can check if the return value is null, if yes the user will get an error message. If the result isn't empty, I will get the template of the template table:

SELECT * FROM templates WHERE id = 0

But this approach doesn't seems really nice to me. Can I combine both statements in one querie? The return value should be the template or null if the user has no template subscription for this template.

Answer 1

You can have them both together using a where condition. Simply there is output if the user has permission.

SELECT * 
FROM templates 
WHERE id = 0 and 1=(SELECT 1 FROM templatesubscription WHERE user_id = 10 AND template_id = 0)

Answer 2

Learn about joins, here's a good overview.

So in your case, you want to inner join between subscriptions and templates, to only get the subscriptions the user has access to:

SELECT t.* -- it's better to list all the columns than use *
  FROM templates t
 INNER
  JOIN templatesubscription ts
    ON t.id = ts.template_id
 WHERE ts.user_id = 10
   AND ts.template_id = 0

So this will return the row(s) from templates table only if there is a record in templatesubscription for that user_id.