0
I have the following select:
SELECT suprimento, public.tiposuprimento.descricao, capacidade,
count(public.estoque.codigosuprimento) quantidade,estoqueminimo, sum (valorunitario) valor
FROM public.estoque,public.tiposuprimento, public.suprimento
WHERE public.estoque.codigosuprimento = public.suprimento.codigo and
public.tiposuprimento.codigo = public.suprimento.codigotiposuprimento and usado = '0'
AND codigosuprimento = 155
GROUP BY suprimento, public.tiposuprimento.descricao,capacidade,estoqueminimo
ORDER BY suprimento
That returns me correctly the following information:
But I need to add what equipment is compatible with this supply, so I created the following Query
SELECT codigosuprimento, array_to_string(array_agg(modelo),' | ') AS modelo
FROM public.modelosuprimento, public.impressoramodelo
WHERE public.impressoramodelo.codigo = public.modelosuprimento.codigomodelo
AND codigosuprimento = 155
GROUP BY codigosuprimento;
And she returns me the equipment correctly:
But I can’t seem to unite these Querys or make them in a select only, whenever I try to adapt to a select only my result is inconsistent with the correct information. I’ve tried it this way:
SELECT suprimento, public.tiposuprimento.descricao, capacidade,
count(public.estoque.codigosuprimento) quantidade,estoqueminimo, sum (valorunitario) valor,
array_to_string(array_agg(modelo),' | ') AS modelo
FROM public.estoque,public.tiposuprimento, public.suprimento, public.modelosuprimento, public.impressoramodelo
WHERE public.estoque.codigosuprimento = public.suprimento.codigo
AND public.estoque.codigosuprimento = public.modelosuprimento.codigosuprimento
AND public.modelosuprimento.codigomodelo = public.impressoramodelo.codigo
AND public.tiposuprimento.codigo = public.suprimento.codigotiposuprimento
AND usado = '0'
AND public.modelosuprimento.codigosuprimento = 155
GROUP BY suprimento, public.tiposuprimento.descricao,capacidade,estoqueminimo
ORDER BY suprimento
And I got the following result:
Which is incorrect, if you have any idea of what I’m doing wrong or any hint of how to achieve this goal, I’d appreciate it.
In view of the problem is that you are using
WHERE
to list the tables, and if case 1 of them has no match, the value is not returned. UseJOINS
to make that union. If you have it in sqlfiddle do select for you.– rbz
@RBZ how can I replace with JOIN in this case?
– R.Santos
You need to download the database script, with the creation of tables, Inserts, etc., so it generates your database in the fiddle, then you pass the link. http://sqlfiddle.com/ for example http://sqlfiddle.com/#! 9/a6c585/1
– rbz
@RBZ You would have to put an example of how to use the JOIN dai I adapt to my need?
– R.Santos
put the structure of tables
– Rovann Linhalis
@R.Santos I published it in response. Have a look at https://answall.com/questions/274433/selecionar-somente-tuplas-de-uma-tabela-com-join/274447#274447
– rbz