How do I do INNER JOIN in this case?

Question

I’m a beginner in Postgresql and I need to make a INNER JOIN to relate data from two tables. It took me a long time to understand this FOREIGN KEY, but I made the right reference in the database. Now, I have a C# application and I don’t know how to write the table reading and data relation command.

Follows the current command structure:

SELECT * FROM users_data WHERE user_id = {0}

What leaves me in doubt is where to put the INNER JOIN, the AS, and the ON. The table users_data has a column user_type and this value must be alternated with the table users_type.

Answer 1

You should do it like this:

SELECT * 
FROM users_data a
INNER JOIN users_type b ON b.id = a.user_type_id 
WHERE user_id = {0}

This taking into account that your table "users_data" has the field "user_type_id" which is your Foreign key.

For you to understand better:

"INNER JOIN" is using for you to join two tables. You could have several "Inner Join" in a query, in which case you are joining several tables in the same query.

"ON" is used right next to the INNER JOIN clause to indicate the fields that relate.

"AS" is used to rename the fields of your query. In this case it was not used.

Abs!

Answer 2

Do so:

SELECT * 
FROM users_data 
     INNER JOIN user_type AS NomeAlias ON users_data.valor = NomeAlias.valor 
WHERE user_id = {0}

Where valor would be the relationship between the tables, such as an ID or something similar.

Other Example:

SELECT p.descricao, p.preco_custo, preco_venda, p.qtd_estoque, 
       p.qtd_estoque_minimo, marca.nome 
FROM produto as p
     INNER JOIN marca AS marca ON marca.id = p.id_marca
ORDER BY marca.nome

But it needs to be seen what kind of relationship you want to make.

There are differences between Join, Left Join, Right Join and Inner Join.