Select in two tables with data from the first table?

Question

Select in two tables with data from the first table?

Navigation

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

1

In a table that contains some comments, one of the columns holds the user ID that made this comment, how can I retrieve that user's data with the ID when making a query in the comment table? I know I can do two select one after another, but if possible I would like to retrieve all the data in a select only.

Comments 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 you select?

php mysql sql

asked by anonymous 04.12.2016 / 20:18

2 answers

1

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

In your case it is ideal to do the ordered sort in the recent comment date for the old ones, and check if it really has a comment has some content

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

06.12.2016 / 13:01

Add code before / head and after body How do I retrieve a line in my jTable?

score 2 · Accepted Answer

For this, you only need to make a JOIN that will bind one table to another according to id_usuario :

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

If you have a constraint for WHERE it looks like this:

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