Getting data from first table by using the data from second table

user_18571370 0
Viewed 37

First of all, I could not find appopriate name for the question, if anyone can find a better name, I would be grateful if they edit. I am new at SQL, and I have the following question: I have two tables as stated below

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

I need to create a query that returns Name of the member as well as their supervisors name that work in WorkPlace 'X'.

I am trying to use inner join but I am not able to achieve what i am trying to. The main question is, how can I select by WorkPlace name, and obtain both members name and supervisor's name.

Supervisor is also a member, so, WorkPlace.SupervisorUID should match with 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 can I get supervisors name from taking uid from workplace, then going member table, find uid that matches with that supervisor uid and get the name.

Member
Jeremy 123 421
Jack 421 421

WorkPlace
1 France 421

I want to return the following table,

Jeremy Jack
mysql sql
1 Answers

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

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 supervisors are not displayed.

You could also remove SupervisorUID from Member and use this:

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
user_7983460
Related