MySQL query to get all users belonging to an organization

I have a fairly large database (that I didn’t design) that I need to get some data from. I need to find all users belonging to an organization. There is a users table that looks like this;

id
name
email
password
more fields

and an organisation table like this;

id
name
org_number
contact_person
more fields

and a user_orgs table through which the other tables are related;

id
user_id
org_id

Given an org_number, I would like to be able to get the name and email address of each user in that organization.

I have tried

SELECT 
   per.name, 
   per.email, 
   org.name AS orgname, 
   org.org_number, 
   org.contact_person 
FROM users AS per 
LEFT JOIN user_orgs AS uorgs 
ON per.id=uorgs.org_id 
LEFT JOIN organization AS org 
ON uorgs.org_id=org.id 
WHERE org.org_number=123456;

Along with a bunch of other queries and I know there should be 20 users registered to the organization number 123456, but it returns empty.

How can I do this?

Answer

Your JOIN conditions do not look correct:

SELECT u.name, u.email, 
       o.name as orgname, o.org_number, o.contact_person 
FROM users u JOIN
     user_orgs uo 
     ON u.id = uo.user_id JOIN
------------------^  -- this changes
     organization o
     ON uo.org_id = o.id 
WHERE o.org_number = 123456;

Advertisement

Answer