How to join two tables in a JSON

Question

I have two tables:

Companies

id: integer
nome: string

Imagery

id: intger
idEmpresa: integer
url: string

Do:

select * from empresas where id = 1

select * from imagens where idempresa = 1

and I get a json file like this:

json_encode($resultado);

It works well, but I always have to make two queries (one in each table).

How to make Json be generated like this:

[{id: 1,
  nome: 'empresa1',
  imagens: {id: 1,
            idEmpresa: 1
            url: '1111'},
           {id: 2,
            idEmpresa: 1
            url: '2222'}
}]

I don’t know how to write the code well in json, but I think you can understand that you should see all the images that belong to each company.

I’ve tried using a Join, but it generates like this:

[{"id":"1","nome":"empresa1","idEmpresa":"1","url":"1111"},
{"id":"1","nome":"empresa1","idEmpresa":"1","url":"2222"},
{"id":"1","nome":"empresa1","idEmpresa":"1","url":"3333"}]

That is, generates more than one record for each company

Some hint on how to join the tables so that the result turns out as I wish?

Answer 1

You can use the UNION to join the two tables.

SELECT 
    Empresas.id,
    Empresas.nome,
FROM 
    Empresas
UNION ALL
SELECT
    imagens.idEmpresa,
    imagens.url,
FROM 
    Imagens
WHERE Empresas.id = imagens.idEmpresa