How to build sql query from two tables?

Members is a table of persons who are members of a club. Various details of each member are included in this table through various columns such as ID, name, address, email, etc.

Now, any two members of this table can have a relationship. For example, if A, B, C, and D are all members of this table, then A and B can have a relation; A can C have a relation; B and D can have relations, etc. So, while member A can have a relation with all other members (such as B, C, and D, etc.), he can have a relation with any other member in, either way, i.e., it can be A-B or B-A. For this relationship, a new table Relations has been created. This has only 3 columns; namely, ID, FirstMemberID, SecondMemberID. Here, both FirstMemberID and SecondMemberID are basically the respective IDs of members (in a relationship) from the Members table.

Now, I want to construct an SQL query (for MySQL) to select full details (all columns) from the Members table for all those members who have a relationship with a person with ID of XYZ in such a manner that XYZ is the first part of this relationship. Here XYZ is only the ID of the person and not his name. So, I can this first query:

SELECT SecondMemberID FROM Relations WHERE FirstMemberID = XYZ;

This query gives multi-results because XYZ has relations with many members. The other select statement is like this:

SELECT * FROM Members WHERE ID = SecondMemberID;

[Note: Here, SecondMemberID is what is returned from the first query. But, as I said, there can be many SecondMemberID here.]

My problem is how to combine the two queries in such a way that I get proper results.

I have tried various methods, like treating the results of the first query as an array and then using IN operator in the second query by using the implode method of PHP or otherwise. I have also tried various JOIN statements but without success.

What am I missing? Can you please help?

Answer

I think you want:

SELECT SecondMemberID 
  FROM Relations 
 WHERE Id IN (SELECT SecondMemberID 
                FROM Members
               WHERE FirstMemberID = 'XYZ');

Advertisement

Answer