How do I get specific data using a SQL select query?-CodePudding

I'm working on a project in which I built the following logical model :

How do I create a search query to return specific values from all three tables regarding one entity?

For instance, let's suppose I want both nomeVinho and anoVinho values from Vinho, as well as the nomeVinicola from the table in which this entity is related to and the nomeRegiao value from which this last table is related to.

It would be something like this :

select nomeVinho,anoVinho,nomeVinicola,nomeRegiao from Vinho

This does not work because I don't think I can extract things from other tables without mentioning them on the query, so how would I do it?

CodePudding user response：

Assuming entity are Vinhos, you'll need to join the tables. Note that there are common names between each linked table, the common name is the join key. Answering your specific question

SELECT nomeVinho, anoVinho, nomeVinicola, nomeRegiao
FROM Vinho
LEFT JOIN Viniciola
ON Vinho.codViniciola = Vinicola.codVinicola
LEFT JOIN Regiao
ON Regiao.codRegiao = Vinicola.codRegiao

Edit: be aware there are difference between Joins (left, right, inner) and they may affect your outcome.