Obtain records of several tables without duplicates in postgresql

Question

Obtain records of several tables without duplicates in postgresql

Navigation

#1 by (3 votes)

1

I'm having a problem when it comes to bringing the database records, I have an inventory system of some technological equipment, each one has an asset code, but an asset code can be on several computers since an asset can be made up of several computers for example: cpu, keyboard, mouse, etc.

At the same time there are teams that do not have an asset code, so the code of them is "N / A".

The problem is that when extracting the data from several tables, I am having too many duplicates.

I have the following query:

select activo_fijo.id_activo,codigo_activo, codigo_ebye, 
(select marca_modelo_idcpu(cpu.id_cpu) as cpu), 
(select marca_modelo_idlaptop(laptop.id_laptop) as laptop),
cpu.id_cpu, laptop.id_laptop
from activo_fijo 
left join cpu on cpu.id_activo = activo_fijo.id_activo 
left join laptop on laptop.id_activo = activo_fijo.id_activo
where (laptop.id_laptop IS NOT NULL or cpu.id_cpu IS NOT NULL)

This gives me the following results:

As you can see, the registers are repeating n times for each cpu with "N / A" code and n times for each laptop with "N / A" code

I tried too:

select activo_fijo.id_activo,codigo_activo, codigo_ebye, 
(select marca_modelo_idcpu(cpu.id_cpu) as cpu), 
(select marca_modelo_idlaptop(laptop.id_laptop) as laptop),
cpu.id_cpu, laptop.id_laptop
from activo_fijo 
full outer join cpu on cpu.id_activo = activo_fijo.id_activo 
full outerjoin laptop on laptop.id_activo = activo_fijo.id_activo
where (laptop.id_laptop IS NULL or cpu.id_cpu IS NULL)

But that query returns all the cpu and laptop that do not have "N / A" code, so equipment is lost.

How could I eliminate those duplicates? Without losing data.

The schema of the database is as follows:

EDIT WHAT I WANT TO GET

This is what I get now:

This is what I hope to get:

That is to say that the 347 cpus are listed, then the laptops, and the other equipment, but without repeating, for example, the 346 cpu with the ids of laptops.

sql postgresql

asked by Juan Pinzón 28.06.2016 в 23:30

source

1 answer

validate user attempts when logging in Problem to link buttons with backbone.js

score 3 · Accepted Answer

Considering that you want to independently manage the columns of id_laptop and id_cpu, a simple way to solve it is with UNION and adding a 'false' column with null value to each query:

SELECT activo_fijo.id_activo, codigo_activo, codigo_ebye, 
(select marca_modelo_idcpu(cpu.id_cpu) as cpu), 
(select marca_modelo_idlaptop(laptop.id_laptop) as laptop),
cpu.id_cpu, null AS laptop.id_laptop
FROM activo_fijo 
LEFT JOIN cpu on cpu.id_activo = activo_fijo.id_activo 
WHERE cpu.id_cpu IS NOT NULL
UNION
SELECT activo_fijo.id_activo, codigo_activo, codigo_ebye, 
(select marca_modelo_idcpu(cpu.id_cpu) as cpu), 
(select marca_modelo_idlaptop(laptop.id_laptop) as laptop),
null AS cpu.id_cpu, laptop.id_laptop
FROM activo_fijo 
LEFT JOIN laptop on laptop.id_activo = activo_fijo.id_activo
WHERE laptop.id_laptop IS NOT NULL

If the 'false' column is not defined, there would be only 6 columns left and the values of id_cpu and id_laptop would be in it (without duplicates).