Inter table joins for a relational chord diagram

Question

I have one question. It may be easy for you.

I am trying to build a d3js chord diagram - something like this (http://bl.ocks.org/4062006):

I am however getting the data from my mysql database

My table looks like this:

id gender_taker gender_giver
1     F            M
2     M            M
3     F            M
4     F            F

I want the output to look something like this:

gender_giver gender_taker count(*)
M            F            2
M            M            1
F            F            1

This is easy and can be produced by:

SELECT gender_giver, gender_taker, COUNT(*) FROM data WHEREclauses GROUP BY gender_taker, gender_giver

But I have another issue, I have two another tables that looks like this: Table 1:

id entryid gender_taker
1   2       F
2   2       M
3   3       F

Table 2:

id entryid gender_giver
1   1       M
2   1       F
3   2       M

entryid is basically id of the first table suggesting that Table2 and Table3 are just subsets of table1

If you combine these three tables it might looks something like:

id gender_taker gender_giver
1     F            M,M,F
2     M,F,M        M,M
3     F,F          M
4     F            F

So as a result for the chord diagram I want all these tables taken into account eventually giving something like:

gender_giver gender_taker count(*)
M            F            6           
M            M            4
F            F            2
F            M            0

Please help me out here.

Answer 1

DROP TABLE IF EXISTS core;
CREATE TABLE core
(entry_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,gender_taker CHAR(1) NOT NULL
,gender_giver CHAR(1) NOT NULL
);

INSERT INTO core VALUES
(1     ,'F','M'),
(2,'M','M'),
(3,'F','M'),
(4,'F','F');

DROP TABLE IF EXISTS table1;
CREATE TABLE table1
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,entryid INT NOT NULL
,gender_taker CHAR(1) NOT NULL
);

INSERT INTO table1 VALUES
(1   ,2       ,'F'),
(2   ,2       ,'M'),
(3   ,3       ,'F');


DROP TABLE IF EXISTS table2;
CREATE TABLE table2
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,entryid INT NOT NULL
,gender_giver CHAR(1) NOT NULL
);

INSERT INTO table2 VALUES

(1   ,1       ,'M'),
(2   ,1       ,'F'),
(3   ,2       ,'M');

SELECT entry_id
     , GROUP_CONCAT(gender_taker) gender_takers
     , GROUP_CONCAT(gender_giver) gender_givers
  FROM 
     ( SELECT * FROM core
       UNION
       SELECT entryid,gender_taker,NULL FROM table1
       UNION
       SELECT entryid,NULL,gender_giver FROM table2
     ) x
 GROUP 
    BY entry_id;
+----------+---------------+---------------+
| entry_id | gender_takers | gender_givers |
+----------+---------------+---------------+
|        1 | F             | M,M,F         |
|        2 | M,F,M         | M,M           |
|        3 | F,F           | M             |
|        4 | F             | F             |
+----------+---------------+---------------+

SELECT a.gender taker
     , b.gender giver
     , COUNT(*)
  FROM 
     (
       SELECT entry_id,'taker' role, gender_taker gender FROM core
       UNION ALL
       SELECT entry_id,'giver', gender_giver FROM core
       UNION ALL
       SELECT entryid,'taker',gender_taker FROM table1
       UNION ALL
       SELECT entryid,'giver',gender_giver FROM table2
     ) a
  JOIN
     (
       SELECT entry_id,'taker' role, gender_taker gender FROM core
       UNION ALL
       SELECT entry_id,'giver', gender_giver FROM core
       UNION ALL
       SELECT entryid,'taker',gender_taker FROM table1
       UNION ALL
       SELECT entryid,'giver',gender_giver FROM table2
     ) b
    ON b.entry_id = a.entry_id
   AND b.role = 'giver'
   AND a.role = 'taker'
 GROUP 
    BY taker
     , giver;
+-------+-------+----------+
| taker | giver | COUNT(*) |
+-------+-------+----------+
| F     | F     |        2 |
| F     | M     |        6 |
| M     | M     |        4 |
+-------+-------+----------+