How to take the last inserted values from two tables, and sort them by the last access?

Asked

Viewed 102 times

0

I need to create a select who picks up the id's of two tables, unite them and show me the last record of the two together. I tried so:

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 cannot be equal.

Structure table 1

 id_acesso / cod_mobilibus / dt_acesso

Structure table 2

 id_ponto / cod_mobilibus / nome / endereco / cidade / bairro / codigo
sql phpmyadmin select-sql

  • can post the structure of the two tables?

  • Oops, updated the post.

  • as such the results cannot be equal?

  • I will explain in more detail. I have several qrcodes where the person accesses and takes her to a website. This entry records the value in the database in table 1. In table 2 are the definitions of the "points" of table 1. So I need the last record of each point, but cod_mobilibus cannot be repeated, because two users can access this qrcode, but I just want the most recent access of it.

2 answers

1


Try it this way, buddy:

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

In the INNER JOIN make a SELECT to bring the data you want to merge with your other table, in which case you wanted only one tupla which had the earliest date. Then a SELECT using the function MAX() to pass and catch the last dt_acesso and a GROUP BY for cod_mobilibus to get only one piece of information from each. Once this is done, just finish the INNER JOIN, comparing the tables by cod_mobilibus.

  • Thank you so much for the reply! It was very helpful for me.

0

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

select * from Tabela1 as t1 Inner Join tabela2 as t2 on t1.cod_mobilibus = t2.cod_mobilibus

Where t1.dt_access = (select tmp.dt_access from Tabela1 as tmp Where tmp.cod_mobilibus = t1.cod_mobilibus order by desc limit 1)

order by t1.dt_access

  • I tried to use this code, but even if I change some things, just return me select * from pr_qr_pontos as t1 inner join pr_pontos as t2 on t1.cod_mobilibus = t2.cod_mobilibus

where t1.dt_acesso = (select dt_acesso from pr_qr_pontos order by desc limit 1)

order by t1.dt_acesso Or else when I add dt_access after order by returns it Subquery returns more than 1 row

  • I made a change. Try again!

  • I didn’t get what I wanted, I’ll try another question. But thanks for the help!

Browser other questions tagged sql phpmyadmin select-sql

You are not signed in. Login or sign up in order to post.