Multiple Table Joins with WHERE clause

Question

I'm using Mysql and I'm having a difficult time trying to get the results from a SELECT query. I am having 3 tables. First table sections, second table section members and third table section member status(data in this table is static).

select * from Sections;

| section_id | title  | description | section_ownerid |
-------------------------------------------------------
| 1          | title1 | desc1       | 100             |
| 2          | title2 | desc2       | 100             |
| 3          | title3 | desc3       | 100             |
| 4          | title4 | desc4       | 100             |
| 5          | title5 | desc5       | 100             |
| 6          | title6 | desc6       | 100             |

select * from SectionMembers;

| SectionMembers_id | section_id  | status_code | memberid |
------------------------------------------------------------
| 1                 | 1           | 10          | 200      |
| 2                 | 1           | 20          | 300      |
| 3                 | 2           | 30          | 200      |
| 4                 | 2           | 10          | 300      |
| 5                 | 3           | 30          | 200      |
| 6                 | 4           | 20          | 200      |

select * from MemberStatus;

| MemberStatus_id | status_code  | status         |
---------------------------------------------------
| 1               | 10           | PENDINGMEMBER  |
| 2               | 20           | ACTIVEMEMBER   |
| 3               | 30           | MEMBERREJECTED |

I used joins to get results as

select distinct(a.section_id) as id,
        a.title,
        a.description,
        c.status
 from Sections a 
 left join SectionMembers b on a.section_id = b.section_id
 inner join MemberStatus c on b.status_code = c.status_code
 where (a.section_ownerid = 100 and b.memberid = 200)
       or (a.section_ownerid = 100);

But I am not getting correct result. I want results as shown below:

| section_id | title  | description | status         |
------------------------------------------------------
| 1          | title1 | desc1       | PENDINGMEMBER  |
| 2          | title2 | desc2       | ACTIVEMEMBER   |
| 3          | title3 | desc3       | MEMBERREJECTED |
| 4          | title4 | desc4       | ACTIVEMEMBER   |
| 5          | title5 | desc5       | NULL           |
| 6          | title6 | desc6       | NULL           |

score 21 · Accepted Answer · answered Feb 12 '13 at 10:57

It seems like the following query is what you need. Notice that the filter for memberid = 200 has been moved to the join condition:

select s.section_id,
  s.title,
  s.description,
  m.status
from Sections s
left join SectionMembers sm
  on s.section_id = sm.section_id
  and sm.memberid = 200
left join MemberStatus m
  on sm.status_code = m.status_code
where s.section_ownerid = 100;

Note: while your desired result shows that section_id=2 has a status of ActiveMember there is no way in your sample data to make this value link to section 2.

This query gives the result:

| SECTION_ID |  TITLE | DESCRIPTION |         STATUS |
------------------------------------------------------
|          1 | title1 |       desc1 |  PendingMember |
|          2 | title2 |       desc2 | MemberRejected |
|          3 | title3 |       desc3 | MemberRejected |
|          4 | title4 |       desc4 |   ActiveMember |
|          5 | title5 |       desc5 |         (null) |
|          6 | title6 |       desc6 |         (null) |

Alex Kolarski · Answer 2 · 2013-02-12T11:03:56.070

2

Here you go:

SELECT s.section_id, s.title, s.description, ms.status 
FROM Sections as s
LEFT JOIN SectionMembers as sm ON s.section_id = sm.section_id
LEFT JOIN MemberStatus as ms ON ms.status_code = sm.status_code
WHERE (s.section_ownerid = 100 and sm.memberid = 200)
   OR (s.section_ownerid = 100)
GROUP BY section_id

Note: Updated my answer

edited Feb 12 '13 at 11:03

answered Feb 12 '13 at 10:41

Alex Kolarski

3,255
1
25
35

Multiple Table Joins with WHERE clause

2 Answers2