How to query data from a table using INNER JOIN in a subquery in PHP

I do not know exactly what table structure you have but let's assume this is

tb_usuario
- id_usuario
- nome
- foto

tb_chat
- id_chat
- id_usuario
- tx_msg
- dt_envio

user id being rigid and necessary

SELECT
    U.nome,
    U.foto,
    C.tx_msg,
    C.dt_envio
FROM
    tb_chat C
    INNER JOIN tb_usuario U ON U.id_usuario = C.id_usuario
WHERE
    id_chat = '{$id_chat}'
ORDER BY
    dt_envio DESC

INNER ja restricts QUERY to return only results that have user_id

Example

- Lucas     |   perfil1.jpg     | teste msg  | 12/01/2016 08:20:14
- Rafael    |   perfil2.jpg     | teste msg2 | 12/01/2016 08:24:37

userid not being rigid

SELECT
    U.nome,
    U.foto,
    C.tx_msg,
    C.dt_envio
FROM
    tb_chat C
    LEFT JOIN tb_usuario U ON U.id_usuario = C.id_usuario
WHERE
    id_chat = '{$id_chat}'
ORDER BY
    dt_envio DESC

The LEFT says to give preference to the content of the left in the tb_chat case, so if there is no link with tb_usuario the columns belonging to it save NULL

Example

-           |                   | teste msg  | 12/01/2016 08:20:14
- Rafael    |   perfil2.jpg     | teste msg2 | 12/01/2016 08:24:37

Note

• If you use LEFT and id_usuario must be rigid, polo will need to be WHERE

  WHERE
      id_chat = '{$id_chat}'
      AND id_usuario IS NOT NULL
  

This depends on the view you are going to create. In this case it can be INNER JOIN for the chat table. Telling that all chats must have a user.

Here's an example:

Select * 
From 'chat' 
INNER JOIN 'usuarios' ON 'chat'.$chave_estrangeira_do_id_entre_aspas = usuarios.id 
ORDER BY 'chat'.'id' ASC LIMIT 10

If you want to show messages without users, for example, you do not need a user to chat or anything, you can use left join .