How to View Query with Inner Join in mysql php

Question

How to View Query with Inner Join in mysql php

Asked 3 years, 9 months ago

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.

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.

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"

– novic

2020/12/23 at 13:50
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.

– anonimo

2020/12/23 at 13:52
error my proprietary.proprietary was to be an init also serving as foreign key

– Renan Teotonio

2020/12/23 at 14:00
how does the distinct work? you know tell me I’ll search better to understand.

– Renan Teotonio

2020/12/23 at 14:01

1 answer

Browser other questions tagged php mysql

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

by Leonardo Sacon • 45 points · Answer 1 · 2020-12-23T14:25:59+00:00

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