Select and Inner Join

Asked

Viewed 63 times

0

I have a problem, I can’t make the appointment mysql return the data from id=4 of the profile, using the query down below:

select p.*, ec.nome as estado_civil_nome , t.nome as titulo_nome,
        uo.nome as uf_oab_nome from perfil p
        inner join estado_civil ec on(p.estado_civil_id = ec.id)
        inner join titulo t on(p.titulo_id = t.id) 
        inner join uf_oab uo on(p.uf_oab_id = uo.id)
        where p.id = {$id};

The answer is: 

MySQL não retornou nenhum registo. (A consulta demorou 0,0000 segundos.)

Whereas the id=4 exists in the database!

I made a echo($query) and showed the following result:

select p.*, ec.nome as estado_civil_nome , t.nome as titulo_nome, uo.nome as 
uf_oab_nome from perfil p inner join estado_civil ec on(p.estado_civil_id = 
ec.id) inner join titulo t on(p.titulo_id = t.id) inner join uf_oab uo 
on(p.uf_oab_id = uo.id) where p.id = 4

Where am I going wrong?

php mysql
  • 1

    as you are using Inner Join, Id 4 has to exist in all tables, otherwise it will not return data, you have arrived if Id 4 is in all tables (all used Join) ?

  • You need all the records of these tables or you can exchange the inner join by a left join ?

  • Thanks for the return Edvaldo and Ronaldo, these records are part of the user profile registration, in this case I am sending an id=4 to rescue all data from the profile table and display on a page some data so that the customer can change. All these data are used in 3 forms, one with :Name, nationality, marital status, rg, Cpf, and date of birth , another with data related to the academic profile and another with professional data. All extracted from the same table

  • Edvaldo id 4 is the profile table id. The tables that make up the Inner Join do not have the profile id

  • Ronaldo actually I make change in 3 steps can I use the left Join for each step? This would make my sql work?

1 answer

0

Thanks to the light of colleagues Edvaldo and Ronaldo, I was able to solve my problem. Simply by changing the INNER JOIN to LEFT JOIN, in this way, when searching (search), mysql compares the table (left) 'profile' to the tables (right) 'state_civil', 'title' and uf_oab, if it cannot do the comparison it returns 'null' to the right tables (right)we can fill these fields with null at the time of the change. Good studies to all!

Browser other questions tagged php mysql

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