Select with wrong result

Asked

Viewed 55 times

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:

inserir a descrição da imagem aqui

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:

inserir a descrição da imagem aqui

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:

inserir a descrição da imagem aqui

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.

postgresql
  • 1

    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. Use JOINS to make that union. If you have it in sqlfiddle do select for you.

  • @RBZ how can I replace with JOIN in this case?

  • 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 You would have to put an example of how to use the JOIN dai I adapt to my need?

  • put the structure of tables

  • @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

Show 1 more comment

2 answers

1

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. Use JOINS to make that union.

Basically you have to understand the concept of left and right (left and right), for example:

Your table in FROM she is your left table, ie, LEFT, when making a JOIN.

FROM tabela1 X
LEFT OUTER JOIN tabela2 Y ON Y.campo1 = X.campo2

I mean, you’re bringing everything from table X, plus the Y records that link to X.

Now in:

FROM tabela1 X
RIGHT OUTER JOIN tabela2 Y ON Y.campo1 = X.campo2

You bring everything that is in your right table "Y" plus those of "X" that has to do with "Y".

The INNER only what is common in the 2 tables, and the FULL brings EVERYTHING independent of relationships.

Link#1 Link#2

1

I solved my problem by performing a subSelect:

SELECT public.estoque.codigosuprimento as cod ,suprimento, public.tiposuprimento.descricao, capacidade, 
count(public.estoque.codigosuprimento) quantidade,estoqueminimo, sum (valorunitario) valor, (SELECT array_to_string(array_agg(modelo),' | ') AS modelo
FROM public.modelosuprimento, public.impressoramodelo where 
public.impressoramodelo.codigo = public.modelosuprimento.codigomodelo and codigosuprimento = public.estoque.codigosuprimento group by codigosuprimento)
FROM public.estoque,public.tiposuprimento, public.suprimento
where public.estoque.codigosuprimento = public.suprimento.codigo and 
public.tiposuprimento.codigo = public.suprimento.codigotiposuprimento and usado = '0' 
group by suprimento, public.tiposuprimento.descricao,capacidade,estoqueminimo,public.estoque.codigosuprimento
order by suprimento

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.