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….
Best Answer