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 can accomplish this via a number of aliased JOIN conditions, as in the following:

```
SELECT 
    Table1.d,
    COUNT(d1.id) AS d1,
    COUNT(d2.id) AS d2,
    COUNT(d3.id) AS d3,
    COUNT(d4.id) AS d4
FROM 
    Table1
     LEFT JOIN 
    Table2 d1 ON 
        Table1.d = d1.D1
     LEFT JOIN 
    Table2 d2 ON 
        Table1.d = d2.D2
     LEFT JOIN 
    Table2 d3 ON 
        Table1.d = d3.D3
     LEFT JOIN 
    Table2 d4 ON 
        Table1.d = d4.D4
GROUP BY Table1.d

```

This will allow you to avoid the overhead of multiple single-value calls to the table, while preserving a single query.