Inner with 3 PHP/SQL tables

Question

I have 3 tables and I want to compare their value, I can do with 2 using the JOIN but when I put JOIN in the third point php doesn’t work.

Code in PHP:

$agora = "select * from usuario u 
          join dia_usuario du 
          on u.usuario_id = du.id_diarista 
          join diarista d 
          on d.id = ud.id_diarista";
$sim = mys[![inserir a descrição da imagem aqui][1]][1]qli_query($conn,$agora);
while ($row_usuario2 = mysqli_fetch_assoc($sim)) {
    $ola2 = $row_usuario2['nome'];
}

User table

Table dia_usuario

Daily table

Return of consultation

Answer 1

Your mistake is in the second equal, you put "ud" and not "du"

$agora = "select * from usuario u 
          inner join dia_usuario du 
          on u.usuario_id = du.id_diarista 
          inner join diarista d 
          on d.id = du.id_diarista";
$sim = mysqli_query($conn,$agora);
while ($row_usuario2 = mysqli_fetch_assoc($sim)) {
    $ola2 = $row_usuario2['nome'];
}

=========

You only find the enerson user because of the following...

You are taking all user data from different tables where the user id is equal to the daily id and the daily table id (not the daily id) is equal to the daily id...

So the user enerson has id 3 which is equal to id_diarista 3 which is equal to the id of the table diarista which is also 3... The other value is 5 and there is no user with id 5 at the time, gave to draw?

Answer 2

What is happening is the fact that you are restricted in your search for the content of the related tables.

What does that mean?

Your Join restricts the returns of the final set by filtering the results that are in the sets related to the main set.

If you want to bring the data that is present in the main table and coalesce what does not exist in the tables B and C of your search, you should use the left Join.

in case, your query would look like this:

select 
* 
from usuario u 
left join dia_usuario du 
  on u.usuario_id = du.id 
left join diarista d 
  on d.id = du.id;

I don’t want to seem crass or pretentious, but I see that you seem to be starting in the studies on database, I could notice that your modeling still does not reflect well the reality of what you want with your application.

I suggest studying a little more how the joins work in the queries, for this follows below a small but interesting article:

http://dan-scientia.blogspot.com/2012/10/teoria-dos-conjuntos-em-juncoes-sql.html

Good luck there in your application, and I hope my contribution will be useful.