Multiple SQL query

Question

I’m not very experienced with SQL language, as I could make a query work this way:

SELECT *,
( SELECT marca.* FROM marca WHERE marca.id = produtos.id ) AS nome_marca
FROM produtos WHERE id = 5

What I want to return is everything * table produtos and all * table marca guarding in nome_marca.

If I try to select only 1 field as well ->( SELECT marca.nome FROM marca WHERE marca.id = produtos.id ) the normal wheel code. But it does not run if I try to select all with *

Answer 1

That solves your problem:

SELECT *, ( SELECT top 1 marca.* FROM marca WHERE marca.id = produtos.id ) AS nome_marca
FROM produtos 
WHERE id = 5

The problem is that the way it was, the sql does not understand that the subconsultation will have only one return (marca.id = produtos.id), therefore the error. When you send only return the top 1, he understands that he will bring only one line.

An alternative would be for you to use join, should have the same result:

SELECT *
FROM produtos
JOIN marca ON marca.id = produtos.id
WHERE produtos.id = 5

---

edited after comment

@Anorak, the use of one more Join is basically what you wrote in comment, the problem is that there is a comma left; but it would look like this:

SELECT * 
FROM produtos 
JOIN categoria ON categoria.id = produtos.id
JOIN marca ON marca.id = produtos.id 
WHERE produtos.id = 5