How to View Query with Inner Join in mysql php

Asked

Viewed 43 times

1

I have these two tables and would like to consult the owners according to the type of properties that has the id_proprietario. inserir a descrição da imagem aqui

what I tried to do was this:

select
    *
from
    proprietario
inner join
    propriedade
on proprietario.id_agente = propriedade.proprietario 
where propriedade.tipo = "Terreno" and exibicao = "Externo" 
 and proprietario.id_agente = "1"

With this I select all that fit in the filter, but do not know how to display the owners of this list without repeating, just once.

If the bank is wrong I can change too, if you can help me.

php mysql

  • just put distinct: select distinct * from proprietario inner join propriedade on proprietario.id_agente = propriedade.proprietario where propriedade.tipo = "Terreno" and exibicao = "Externo" and proprietario.id_agente = "1"

  • As a merge condition do you really want to compare an int(11) (proprietary.id_agent) with a varchar(1000) (proprietary.proprietary property)? Search for foreign key.

  • error my proprietary.proprietary was to be an init also serving as foreign key

  • how does the distinct work? you know tell me I’ll search better to understand.

1 answer

0

For better understanding, if you perform the following instruction:

select distinct(id_agente) from proprietario

you will be preventing some owner from repeating himself in the query, by the agent id in the table.

In my view, I would change the "owner" field of the property table to foreign key id_proprietary for the owner table, field "id_agent".

So you have a relationship between the tables and your query - regardless of Where type condition, agent and display - will be more simplified. The following code can be executed:

select distinct(p1.id_agente) from proprietario p1 
inner join propriedade p2 on p1.id_agente=p2.id_proprietario

  • I understand, I’ll make these changes, thank you.

  • I used these instructions and it worked, it worked perfectly, I’m very grateful for the help.

Browser other questions tagged php mysql

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