0

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....

1
  • Can you a) provide your version of MySQL and b) show us the result you want in tabular form? Even better, you could provide a fiddle with sample data thrown in. p.s. welcome to the forum!
    – Vérace
    Apr 12 '20 at 21:37
-1
#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.
1
  • I'm sorry but you have clearly not read the question in detail and the query you have produced will throw errors if run against a Mysql database.
    – Frank
    Apr 13 '20 at 9:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.