Select in two tables with data from the first table?

Asked

Viewed 309 times

1

In a table that contains some comments, one of the columns stores the ID of the user who made this comment, how can I recover the data of that user with the ID at the time of making a query in the comment table? I know I can make two select one after the other, but if possible I would like to recover all the data in one select.

Comment table:

+----------------+-------------+------+-----+---------+
| id_usuario     | serie       | data | comentario    |
+----------------+-------------+------+-----+---------+
| varchar(255)   | varchar(255)| date | varchar(255)  |
+----------------+-------------+------+-----+---------+

User table:

+----------------+-------------+------+------+
| id_usuario     | apelido     | email       |
+----------------+-------------+------+------+
| varchar(255)   | varchar(255)| varchar(255)|
+----------------+-------------+------+------+

How would the select look?

php mysql sql database

  • It would be interesting to add the structure of the tables, to facilitate the answer.

  • I updated with a part of the two tables

2 answers

2


For that you only need to make one JOIN which will link one table to another according to the id_usuario:

SELECT usr.*,
       com.*
  FROM comentarios com
       INNER JOIN usuarios usr ON usr.id_usuario = com.id_usuario

If you have a restriction by WHERE will look like this:

SELECT usr.*,
       com.*
  FROM comentarios com
       INNER JOIN usuarios usr ON usr.id_usuario = com.id_usuario
 WHERE usr.id_usuario = 1

  • If there is a WHERE clause for the comment table should insert after the "FROM comments"?

  • No, the WHERE always stays at the end. I’ll put an example

  • @Leoletto added in the example

  • Could also change to recover only the nickname of the user who made the comment, as this is information that will be visible in the source code, would not like to leave other user data who commented so easy

  • Yes, you just need to identify the fields you want to return in the first part of SELECT

1

SELECT * 
FROM usuarios u
JOIN comentarios c ON c.id_usuario = u.id_usuario

In your case it is ideal to make the ordered select on the recent comment date for the old ones, and check if really has a comment have any contents

As follows

SELECT * 
FROM usuarios u
JOIN comentarios c ON c.id_usuario = u.id_usuario
WHERE c.comentario <> null
ORDER BY c.data desc

  • In case it would not be necessary to check if there is comment null, as it is only saved in the BD if the comment is not empty thank the concern, I will test your method tb

Browser other questions tagged php mysql sql database

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