Search table field with query result from a different table?

Question

I asked a question recently:

Find lines of the last 24 hours that repeat the most (Mysql)

And it was answered, now I have another question. I do the following to seek from and to of the most repeated lines of the last 24 hours, but there is a problem, from and to is not the data that needed, is like the id of another table, and then needs the field "name" of another table.

SELECT nome, idade 
FROM other_table 
WHERE myid = `from`, myid = `to`(SELECT `from`, `to`, COUNT(*) AS num_clicks
FROM my_rank
WHERE my_rank_data >= NOW() - INTERVAL 1 DAY
GROUP BY `from`, `to`
ORDER BY num_clicks DESC LIMIT 20);

I am trying wrongly as you can see above. But I already get the result I want with the PHP code below, my doubt is if it is possible to achieve the same result only with the query.

$resultQueryClicks = mysqli_query($con, 'SELECT `from`, `to`, COUNT(*) AS num_clicks
                                        FROM moeda_rank
                                        WHERE data_clique >= NOW() - INTERVAL 365 DAY
                                        GROUP BY `from`, `to`
                                        ORDER BY num_clicks DESC LIMIT 20');
        $rankMoeda =  array();
        while($aux = mysqli_fetch_assoc($resultQueryClicks)) {
            $nameFrom = mysqli_query($con,'SELECT xml FROM moeda WHERE moeda = '.$aux["from"]);
            $nameTo = mysqli_query($con,'SELECT xml FROM moeda WHERE moeda = '.$aux["to"]);
            $auxFrom = mysqli_fetch_assoc($nameFrom);
            $auxTo = mysqli_fetch_assoc($nameTo);

            $rankMoeda[] = array(
                "from" => $auxFrom["xml"],
                "to" => $auxTo["xml"]
            );
        }

Answer 1

You could do something like this, to return all the fields you want in a single query:

SELECT `r.from`, `r.to`, COUNT(r.*) AS num_clicks
     , mf.xml AS xmlFrom, mt.xml AS xmlTo
FROM moeda_rank AS r
INNER JOIN moeda AS mf ON mf.moeda = `r.from`
INNER JOIN moeda AS mt ON mt.moeda = `r.to`
WHERE r.data_clique >= NOW() - INTERVAL 1 DAY
GROUP BY `r.from`, `r.to`, mf.xml, mt.xml
ORDER BY num_clicks DESC
LIMIT 20;

But I don’t know if it would have any negative impact on performance because you have to group the fields as well xml table coin (the content of this field is actually an XML file? ). All of a sudden the way you did might be better, but do some performance tests and see what you think.

I don’t know if you already know the concept of Joins, but what they do is combine the lines of two tables, or more, by linking one, or more, specific fields.

In this case, we’re taking lines from the table moeda_rank but we are asking for the query to combine these lines as the table rows coin, and we are linking the two tables across the field moeda_rank.from and the countryside currency..

As we also want to get the table rows coin that connect with the field moeda_rank.to, we do a second JOIN.

Only, like the fields xmlFrom and xmlto were added to the list of fields returned by the SELECT clause, they should also be added to the GROUP BY clause.