Connection between 3 tables to retrieve information, sql

Question

Connection between 3 tables to retrieve information, sql

Navigation

#1 by (1 votes)

0

I have 3 tables with this structure

Itriedbutcouldonlygetintotwotables,butIwouldneedtobeinthe3to"save / clean" code.

It is necessary to enter in the table helpcenter_topics to get a 20 topics plus recentemente inseridos , get the username of those who posted taking into account that in the helpcenter_topics table the uid column represents idu associative that binds it to the users table and also get the username from the last person who posted a response on that topic I take into account that the topics_answers table is where the responses are stored and that topic_id is id associative linking helpcenter_topics

How to accomplish this in a single line of code?

mysql sql

asked by anonymous 04.06.2015 / 01:46

1 answer

Menu does not change the width XAMPP - error with Apache

score 1 · Accepted Answer

~~One remark, you are not saving the response time so you have no way of knowing who the last one was.~~ Try this:

SELECT ht.title, upost.username, 
       (SELECT ureply.username FROM topics_answers ta
        INNER JOIN users ureply on ureply.idu = ta.uid
        WHERE ta.topic_id = ht.id /* filtra somente do tópico atual */
        ORDER BY ta.id DESC /* resposta mais recente */
        LIMIT 1)
FROM helpcenter_topics ht
INNER JOIN users upost on upost.idu = ht.uid
LIMIT 20