CODEWITHSUNDEEP
mysqljoin
Joshua H.
Joshua H.

Reputation: 87

MySQL - How to Join these tables

I have the following tables:

company

id | name    | domain
1  | sample1 | one.sample.lan
2  | sample2 | two.sample.lan

permissions

id | moduleid | permid
5  | 12       | 2

If permissions for a module are set to a company in permissions-table there will be set the id of the module (here 12) and the id of the company (here 2) the normal id of permissions is auto incremented. If the permissions will be removed the full row will be deleted from permissions.

I want to get the following from my select:

company.name | company.domain | selected

I want to get all companys, doesnt matter if they are set or not set. With the Information if they are selected (in the table permissions) as 1 or 0.

Something like: 

SELECT company.name, company.domain 
FROM company LEFT JOIN permissions ON (company.id = permissions.permid) 
WHERE permissions.moduleid = 12;

Thanks in advance.

Upvotes: 0

Views: 58

Answers (1)

Shadow
Shadow

Reputation: 34231

Use distinct to get each company only once, and use if or case structure to decide if it exist in the permissions table:

SELECT DISTINCT company.name, company.domain, if(permissions.permid is null, 0, 1) as selected
FROM company LEFT JOIN permissions ON (company.id = permissions.permid) 
WHERE permissions.moduleid = 12;

UPDATE: If you want to check which companies have access to a specific module, then move the where criteria into the join condition:

SELECT DISTINCT company.name, company.domain, if(permissions.permid is null, 0, 1) as selected
FROM company LEFT JOIN permissions ON (company.id = permissions.permid) and permissions.moduleid = 12;

Upvotes: 2

Related Questions