SELECT with two Foreign key, return the two tables even looking for only one of the keys

Asked

Viewed 229 times

1

I have a table that has two FK to reference the same table. By selecting all elements that have one of the two FK, using the query: 

SELECT "Proposta".id, "Proposta".id_segurado, "Proposta".data_implantacao, "Proposta".data_assinatura, "Proposta".status,
"Segurado".documento, "Segurado".nome,
"Produtor".id, "Produtor".nome, "Produtor".codigo, "Produtor".documento
FROM "Proposta"
LEFT JOIN "Segurado" ON "Proposta".id_segurado = "Segurado".id
LEFT JOIN "Produtor" ON "Produtor".id = "Proposta".id_produtor1 OR "Produtor".id = "Proposta".id_produtor2

I get the data from the two Fks, but when I do a WHERE to find an offer to find the producer who owns that document, I get the data from only one of the Fks and not from the two as before, as I would to receive both data?

mysql sql database postgresql query

  • I don’t understand your question. Try to post an example of what you say is the return before (that would have worked) and what went wrong, remembering that you are using LEFT OUTER JOIN and not INNER JOIN and therefore it is normal that there is no data from the table "Insured" and/or table "Producer".

  • When I run the query without WHERE it returns me two lines for each proposal, the only difference being the producers' data. If I put a WHERE for example "Product". codigo = '35125' it returns the proposal only with the data of this producer and does not bring the data of the second id_productor2

  • I think you’re getting confused. If your proposal can be related to two producers (identified by the id_productor1 and id_productor2 fields of your table "Proposal") then you need to do a LEFT OUTER JOIN with the table "Producer" in the role of productor1 and another LEFT OUTER JOIN with the same table "Producer" but now in the role of productor2.

  • I tested using LEFT OUTER JOIN and the result was the same. The point is that when using a WHERE, in the end, it returns the data of only one Producer and not both. SELECT ... FROM "Proposal" LEFT OUTER JOIN "Insured" ON "Proposal". id_insured = "Insured". id LEFT OUTER JOIN "Producer" ON "Producer". id = "Proposal". id_productor1 OR "Producer". id = "Proposal". id_productor2 WHERE "Producer". code = '8002866'

1 answer

1


For your explanations that what you want is:

SELECT  "Proposta".id,  "Proposta".id_segurado, "Proposta".data_implantacao, "Proposta".data_assinatura, "Proposta".status,
            "Segurado".documento, "Segurado".nome,
            p1.id, p1.nome, p1.codigo, p1.documento, p2.id, 
            p2.nome, p2.codigo, p2.documento
FROM "Proposta"
LEFT JOIN "Segurado" ON "Proposta".id_segurado = "Segurado".id
LEFT JOIN "Produtor" p1 ON p1.id = "Proposta".id_produtor1
LEFT JOIN "Produtor" p2 ON p2.id = "Proposta".id_produtor2

In other words, "Producer" participating in the query with two roles.

  • That’s right, in the end I only used a WHERE to search for the specific data. Thank you very much, your reply was of great help!

Browser other questions tagged mysql sql database postgresql query

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