How do I get the last values entered from two tables, and sorts them by the last access?

Question

How do I get the last values entered from two tables, and sorts them by the last access?

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I need to create a select that takes the id's from two tables, unite them and show me the last record of the two together. I tried this way:

SELECT DISTINCT p.cod_mobilibus, p.nome, q.dt_acesso
FROM pr_pontos p
RIGHT JOIN pr_qr_pontos q ON p.cod_mobilibus = q.cod_mobilibus
ORDER BY q.dt_acesso DESC

My biggest problem is that the results I receive can not be the same.

Table 1 Structure

access_id / cod_mobilibus / dt_acesso

Table 2 structure

point_id / cod_mobilibus / name / address / city / neighborhood / code

sql sql-select phpmyadmin

asked by anonymous 23.07.2018 / 23:02

2 answers

0

I believe that with a subquery you can ensure that you are getting the last result of each record.

select * from table1 as t1 inner join table2 as t2 on t1.cod_mobilibus = t2.cod_mobilibus

where t1.dt_acesso = (select tmp.dt_acesso from table1 as tmp where tmp.cod_mobilibus = t1.cod_mobilibus order by desc limit 1)

order by t1.dt_acesso

24.07.2018 / 05:14

Send Header from another domain in php C socket problems

score 0 · Accepted Answer

Try this way friend:

SELECT  *
FROM    exemplo2 e2 INNER JOIN
        (
            SELECT  exemplo1.id_acesso, exemplo1.cod_mobilibus,
                    MAX(exemplo1.dt_acesso) dt_acesso
            FROM exemplo1
            GROUP BY exemplo1.cod_mobilibus
        ) e1 ON (e1.cod_mobilibus = e2.cod_mobilibus)

Explanation

No INNER JOIN make a SELECT to bring the data you want to join to your other table, in which case you just wanted a tupla that had the newest date. Then a SELECT using the MAX() function to pass and get the last dt_acesso and a GROUP BY by cod_mobilibus so that you get only one information each. This is done by just finishing INNER JOIN , comparing tables by cod_mobilibus .