SQLite splitting multiple selects into different columns-CodePudding

I need to achieve the following looking output in SQL:

chair | Secretary | Year
------------------------
Matt  |   Susan   | 2006
Susan |   Joe     | 2005

From a database with tables Members and Leaders M

MEMBERS:

Name  | num
-------------
Matt  |  123
Susan |  456
Joe   |  789

LEADERS:

Year | Chair | Secretary
-------------------
2006 | 123   |  456
2005 | 456   |  789

So far I've come up with something like this.

SELECT * FROM 
    (SELECT m.name FROM Members M, Leaders L
     WHERE M.num = L.secretary
          UNION ALL 
    SELECT m.name FROM MEMBER M, Leaders L WHERE M.num = L.chair
          UNION ALL
    SELECT L.year from Leaders L);

However this selects all of the wanted parameters as one column.

My question: how do I make it so that the names in particular are split into Chair and Secretary Columns when they are derived from the same table?

Thank you in advance! I'm quite new to SQL so sorry if this is a rookie mistake haha, I've tried looking online for hints for quite a while now.

CodePudding user response：

In my opinion you could build your query in this way:

SELECT l.year, m1.Name, m2.Name
FROM leaders l
INNER JOIN members m1 ON l.chair = m1.num
INNER JOIN members m2 ON l.secretary = m2.num

You can find a test here