I am still learning SQL and am stumped at this one. I have created two tables (member and relationship). The member tbl has members of the family and the relationship tbl has the relationship of each member to the others. I use and inner join to get the data I need but I cannot get the returned table to give me the answer I want. Here's what I have.
#--THE FIRST TWO QUERIES WORK BUT I NEED TO GET THEM TO WORK TOGETHER--
SELECT
CONCAT(fName, ' ', lName) AS 'Married Member'
FROM member
RIGHT JOIN relationship
ON relationship.member_id = member.id
WHERE relationship.spouse_id;
+-----------------+
| Married Member |
+-----------------+
| John Rock |
| Megan Rock |
| John Rock |
| Cathy Priestap |
| David McMacken |
| Virginia Helman |
+-----------------+
SELECT
CONCAT(fName, ' ', lName) AS 'Spouse'
FROM relationship
INNER JOIN member
ON relationship.spouse_id = member.id;
+-----------------+
| Spouse |
+-----------------+
| Megan Rock |
| John Rock |
| Cathy Priestap |
| John Rock |
| Virginia Helman |
| David McMacken |
+-----------------+
#THIS IS GIVES ME THE RETURNED TABLE I WANT BUT THE SPOUSE NAME IS WRONG--IT SHOULD BE = relationsip.spouse_id INSTEAD OF member.id
SELECT
member.id AS 'Member ID#',
CONCAT(member.fName, ' ', member.lName) AS 'Member Name',
relationship.spouse_id AS 'Spouse ID#',
CONCAT(member.fName, ' ', member.lName) AS 'Spouse Name'
FROM member
INNER JOIN relationship
ON member.id = relationship.member_id
WHERE relationship.spouse_id > 0
ORDER BY member.id;
+------------+-----------------+------------+-----------------+
| Member ID# | Member Name | Spouse ID# | Spouse Name |
+------------+-----------------+------------+-----------------+
| 1 | John Rock | 2 | John Rock |
| 2 | Megan Rock | 1 | Megan Rock |
| 7 | John Rock | 8 | John Rock |
| 8 | Cathy Priestap | 7 | Cathy Priestap |
| 10 | David McMacken | 11 | David McMacken |
| 11 | Virginia Helman | 10 | Virginia Helman |
+------------+-----------------+------------+-----------------+
Any help would be greatly appreciated. And thank you in advance.
