CODEWITHSUNDEEP
phppostgresql
Psyche
Psyche

Reputation: 8773

Problems writing a query to join two tables

I'm working on a script which purpose is to grant site users access to different sections of the site menu. For this I have created two tables, "menu" and "rights":

menu
- id
- section_name

rights
- id
- menu_id (references column id from menu table)
- user_id (references column id from users table)

How can a query be written in order to get all menu sections and mark the ones where a given user has access.

I'm using PHP and Postgres.

Thank you.

Upvotes: 0

Views: 117

Answers (1)

user479911
user479911

Reputation:

Maybe something like this:

SELECT M.*, CASE WHEN R.user_id > 0 THEN 1 ELSE 0 END AS access FROM menu AS M
LEFT JOIN rights AS R ON (R.menu_id = M.id AND R.user_id = 1)

Upvotes: 2

Related Questions