Multiple SQL Query

0

I'm not very experienced with SQL language, how could I 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 all * of table produtos and all * of table marca saving in nome_marca .

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

    
asked by anonymous 07.12.2018 / 19:51

1 answer

2

This 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 as it was, sql does not understand that the subquery will only have a return ( marca.id = produtos.id ), so the error. When you return only top 1 , it understands that it will only contain one line.

One alternative would be to use join , so it 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 a join is basically what you wrote in commentary, the problem is that a comma is 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
    
07.12.2018 / 19:54