Find table field with query result from a different table?

Question

Find table field with query result from a different table?

Navigation

#1 by (0 votes)

0

I recently asked a question:

search for last 24h lines that repeat the most (MySQL)

And it was answered, I now have another question. I do the following to find from and to of the most repeated lines of the last 24h, but there is a problem, from and to is not the data I needed, it is like the id of another table, of the "name" field 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'm trying to erroneously as you can see above. But I already have the result I want with the PHP code below, my question 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"]
            );
        }

php mysql sql query

asked by anonymous 22.02.2018 / 03:38

1 answer

BIRT - Report Parameter - Dynamic Listbox - Select All Proxy to access Webservice [closed]

score 0 · 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 do not know if it would have any negative performance impact because you have to also group xml fields from the currency table (the content of this field is actually a XML file?). Suddenly the way you did it might be even better, but do some performance testing and see what you think.

I do not know if you already know the concept of JOINs, but what they do is combine the rows of two tables, or more, by linking one or more specific fields.

In this case, we are picking rows from the currancy table, but we are asking the query to combine these rows as the lines of the currency table, and we are linking the two tables through field currency_rank.from and the currency.mobity field.

As we also want to get the lines of the currency table that binds with the currencies.to field, we make a second JOIN.

However, since the xmlFrom and xmlTo fields have been added to the list of fields returned by the SELECT clause, they must also be appended to the GROUP BY clause. >