CODEWITHSUNDEEP
sqldatabasepostgresql
Jorge
Jorge

Reputation: 291

Postgresql - Conditional Join if data exist

My current query show the data from the table called "Buque" and has some references from another tables. The problem is when i execute the query it never shows the result because it consumes too much memory i guess.

The current query i have

select buq.buq_codigo, tbu.tbu_codigo, tbu.tbu_nombre, pai.pai_codigo, pai.pai_nombre, 
pue.pto_codigo, pue.pto_nombre, lin.lin_codigo, lin.lin_nombre, tra.tra_codigo, 
tra.tra_nombre, buq.buq_nombre, buq.buq_des, buq.num_trb, buq.num_eslora, 
buq.max_tons, buq.reg_lloyd, buq.buq_codigo1, buq.codigo_omi,
case buq.buq_estado when 'A' then 'Activo' else 'Inactivo' end as buq_estado

from publico.mae_buque as buq, publico.mae_tipbuque as tbu, publico.mae_pais as pai, 
publico.mae_puerto as pue, publico.mae_linea as lin, publico.mae_trafico as tra

where buq.tbu_codigo = tbu.tbu_codigo or
buq.pai_codigo = pai.pai_codigo or
buq.pto_codigo = pue.pto_codigo or
buq.lin_codigo = lin.lin_codigo or
buq.tra_codigo = tra.tra_codigo

I also tried with inner joins but the problem is it returns me the data that meets the conditions of the joins. In other words, if the join has data to compare, returns the name, if not, show the null data.

The query must return me 611 records, with inner joins returns 68 records.

Upvotes: 0

Views: 2929

Answers (2)

Stephan Lechner
Stephan Lechner

Reputation: 35154

Concerning your desired result, use left outer joins, which fill up any non-existing rows of the right hand side table with null-values;

Concerning the out of memory issue, note that you used or to connect your tables; this actually leads to the fact that almost every record of the involved tables is connected to almost every other record (almost a cross join / cartesian product); This can get very large if you connect 6 tables...

select buq.buq_codigo, tbu.tbu_codigo, tbu.tbu_nombre, pai.pai_codigo, pai.pai_nombre, 
pue.pto_codigo, pue.pto_nombre, lin.lin_codigo, lin.lin_nombre, tra.tra_codigo, 
tra.tra_nombre, buq.buq_nombre, buq.buq_des, buq.num_trb, buq.num_eslora, 
buq.max_tons, buq.reg_lloyd, buq.buq_codigo1, buq.codigo_omi,
case buq.buq_estado when 'A' then 'Activo' else 'Inactivo' end as buq_estado

from publico.mae_buque as buq
  left outer join publico.mae_tipbuque as tbu on buq.tbu_codigo = tbu.tbu_codigo
  left outer join publico.mae_pais as pai on (buq.pai_codigo = pai.pai_codigo)
  left outer join publico.mae_puerto as pue on (buq.pto_codigo = pue.pto_codigo)
  left outer join publico.mae_linea as lin on (buq.lin_codigo = lin.lin_codigo)
  left outer join publico.mae_trafico as tra on (buq.tra_codigo = tra.tra_codigo)

Upvotes: 1

arturro
arturro

Reputation: 1606

You have to use left outer join:

select *
    from 
    publico.mae_buque as buq 
    left outer join  publico.mae_tipbuque as tbu on (buq.tbu_codigo = tbu.tbu_codigo)
    left outer join publico.mae_pais as pai on (buq.pai_codigo = pai.pai_codigo) 
    left outer join publico.mae_puerto as pue on (buq.pto_codigo = pue.pto_codigo )
    left outer join publico.mae_linea as lin on (buq.lin_codigo = lin.lin_codigo) 
    left outer join  publico.mae_trafico as tra on (buq.tra_codigo = tra.tra_codigo);

Upvotes: 0

Related Questions