How to join two tables in a JSON

Navigation
0

I have two tables:

Companies 

id: integer
nome: string

Images 

id: intger
idEmpresa: integer
url: string

I do:

select * from empresas where id = 1

select * from imagens where idempresa = 1

and it generates a json file like this:

json_encode($resultado);

It works fine, but I always have to do two queries (one on each table).

How to get Json to be generated like this: 

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

I do not know how to write the code well in json, but I think you can understand that all the images that belong to each company should come.

I have tried to use 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, it generates more than one record for each company

Any tips on how to join the tables so that the result goes as I wish?

    
asked by anonymous 01.02.2018 / 21:45

1 answer

3

You can use 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
    
01.02.2018 / 21:56
RecyclerView Android Loading script asynchronously