Question: I've searched, but I can't find quite what I'm looking for.

I have two tables:

Table1

```
|  d  |      text         |
| 101 | 'description 101' |
| 102 | 'description 102' |
| 103 | 'description 103' |

```

Table2

```
| id |  d1 |  d2 |  d3 |  d4 |
| 01 | 104 | 242 | 102 | 222 |
| 02 | 423 | 553 |     |     |
| 03 | 832 | 142 | 102 |     |

```

etc.

I want a count of how many times each "d" from Table1 is used as the d1, d2, d3, and d4 in Table2. Output would look like this:

```
|  d  | count_d1 | count_d2 | count_d3 | count_d4 |
| 101 |   30032  |   108    |   5002   |   392    |
| 102 |    440   |  5330    |   24     |     5    |
| 103 |     0    |   309    |    2220  |    4     |

```

etc.

I'm sure there's something obvious that I'm just not thinking of, but I've been looking at this for over an hour now, and I got lost in a mess of joins and subqueries.

Answer: 
```
SELECT t1.d,
(
  SELECT COUNT(*) 
  FROM Table2 s2a 
  WHERE s2a.d1 = t1.d
) AS count_d1,
(
  SELECT COUNT(*) 
  FROM Table2 s2b
  WHERE s2b.d2 = t1.d
) AS count_d2,
(
  SELECT COUNT(*) 
  FROM Table2 s2c
  WHERE s2c.d3 = t1.d
) AS count_d3,
(
  SELECT COUNT(*) 
  FROM Table2 s2d 
  WHERE s2d.d4 = t1.d
) AS count_d4
FROM Table1 as t1  

```
You'll have to split this up into 4 queries (1 for each column)

```
SELECT d, sum(Count_d1), sum(Count_d2), sum(Count_d3), sum(Count_d4) FROM (
SELECT d, count(1) AS Count_d1, null AS Count_d2, null AS Count_d3, null AS Count_d4 FROM Table1 LEFT JOIN Table2 on Table1.d=Table2.d1
UNION ALL
SELECT d, null AS Count_d1, count(1) AS Count_d1, null AS Count_d3, null AS Count_d4 FROM Table1 LEFT JOIN Table2 on Table1.d=Table2.d2
UNION ALL
SELECT d, null AS Count_d1, null AS Count_d2, count(1) AS Count_d3, null AS Count_d4 FROM Table1 LEFT JOIN Table2 on Table1.d=Table2.d3
UNION ALL
SELECT d, null AS Count_d1, null AS Count_d2, null AS Count_d3, count(1) AS Count_d4 FROM Table1 LEFT JOIN Table2 on Table1.d=Table2.d4
) GROUP BY d

```