How can I rearrange these two queries to get the second column from inside the IN() How can I get the random.id column from inside this first select

select id, name from random where id IN(1,2,3,4);
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Frank     |
|  2 | Tracey    |
|  3 | Bos       |
|  4 | Charlotte |
+----+-----------+

select `where`, `who`, COUNT(*) from cards where who = "." OR who IN(select name from random where id IN(1,2,3,4)) GROUP BY `who`, `where`;
+-------+--------+----------+
| where | who    | COUNT(*) |
+-------+--------+----------+
| pack  | .      |       78 |
| hand  | Frank  |       15 |
| hand  | Tracey |       15 |
|       | .      |       20 |
+-------+--------+----------+
4 rows in set (0.00 sec)


out when using the second more complicated select, above. Both tables have more columns than show and I do not want to add an extra row to the random table simply to define the . as comes out of the cards table. I think it should be possible to rearrange the second query to provide the desired output of where, id, count but sadly it eludes me.

The results I would like would look like below

+-------+-----------+--------+----------+
| where | random.id | who    | COUNT(*) |
+-------+-----------+--------+----------+
| pack  | NULL      | .      |       78 |
| hand  | 27        | Frank  |       15 |
| hand  | 32        | Tracey |       15 |
|       | NULL      | .      |       20 |
+-------+-----------+--------+----------+


The random.id column can be NULL or a consistent number, zero would be fine, and the who column is only a nice to have. Thanks for the welcome.
The Mysql version is 8.0.19-0ubuntu0.19.10.3 however it may well be better if the version was considered to be broader as I hope to run it on a server over which I have no control.

~~~


Well I have an answer of sorts, not what I was looking for but by divide and conquer two "simpler" queries supply me with the data I need. The data has changed so the numbers in the answers look different but the rules are still the same.

select `where`, 0 AS id, who, COUNT(*) AS cnt from cards WHERE who = "." AND `order` != 0 GROUP BY `where`;
+---------+----+------+-----+
| where   | id | who  | cnt |
+---------+----+------+-----+
| pack    |  0 | .    |  63 |
| dis_top |  0 | .    |   1 |
| discard |  0 | .    |   1 |
+---------+----+------+-----+


The data when there is no who defined.

SELECT `where`, id, who, cnt FROM random AS a RIGHT JOIN (SELECT `where`, who, COUNT(*) AS cnt FROM random AS b INNER JOIN cards AS c ON b.name = c.who GROUP BY `who`, `where`) AS d ON a.name = d.who;
+-------+------+-----------+-----+
| where | id   | who       | cnt |
+-------+------+-----------+-----+
| hand  |    1 | Frank     |  11 |
| hand  |    2 | Tracey    |   7 |
| table |    2 | Tracey    |   2 |
| hand  |    3 | Bos       |  11 |
| hand  |    4 | Charlotte |  11 |
+-------+------+-----------+-----+


The data when there is a who defined.

I have not claimed to have answered my own question as I still think it must be possible to do it a single query, but....
#Inner Join both the tables:

SELECT random.id, `where`, `who`, COUNT(*)
FROM cards
JOIN random
ON cards.who=random.name

#I assume you want all the values from `who` column from table:cards, so there's no need to use WHERE again.