Extract data from first table, using data from second table

Question

First of all, I can't find a suitable name for the question, if anyone can find a better one, I'd be very grateful if they edited it. I am new to SQL and I have the following question: I have two tables as follows

Member -> Name, UID (primary key), SupervisorUID. WorkPlace -> WP_UID (primary key), Name, SupervisorUID.

I need to create a query that returns the names of members who work in WorkPlace 'X' and the names of their superiors.

I tried using inner joins but I couldn't achieve the results I wanted. The main question is, how can I select by WorkPlace name and get the member name and superior name.

The superior is also a member, so WorkPlace.SupervisorUID should match Member.UID

SELECT Member.Name, Y
INNER JOIN WorkPlace on WorkPlace.SupervisorUID = Member.UID
WHERE WorkPlace.Name = 'France'

I need to find what to put on Y.

I need to find how to find the uid that matches that supervisor's uid and get the name by getting the uid from the workplace and then going into the members table.

Member
Jeremy 123 421
Jack 421 421

WorkPlace
1 France 421

I want to return the following form,

Jeremy Jack

Answer 1

I think you need to add WP_UID as a column in Member. I'm not sure if the syntax for mysql is the same, but the following code works in MS SQL Server if you add WP_UID to Member:

SELECT M1.Name, M2.Name
  FROM Member AS M1
  INNER JOIN WorkPlace AS WP on WP.WP_UID = M1.WP_UID
  INNER JOIN Member AS M2 on M2.UID = M1.SupervisorUID
  WHERE WP.Name = 'France' AND M1.UID <> M1.SupervisorUID

I added M1.UID<>M1.SupervisorUID so that the supervisor is not displayed.

You can also remove the SupervisorUID from Member and use the following code:

SELECT M1.Name, M2.Name
  FROM Member AS M1
  INNER JOIN WorkPlace AS WP on WP.WP_UID = M1.WP_UID
  INNER JOIN Member AS M2 on M2.UID = WP.SupervisorUID
  WHERE WP.Name = 'France' AND M1.UID <> WP.SupervisorUID