Postgresql - Conditional Join if data exist

Question

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.

Stephan Lechner · Accepted Answer · 2017-01-09 21:20:17Z

1

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)

edited Jan 9, 2017 at 21:20

answered Jan 9, 2017 at 21:14

Stephan Lechner

35.2k4 gold badges38 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jorge Over a year ago

This returns me the data that exist, but also i have null values on my main Table "Buque", i want to show them all

Stephan Lechner Over a year ago

Is publico.mae_buque the main table, or is there another one, e.g. publico.buque?

Jorge Over a year ago

That is the main table, all that has "mae_" are main tables. These are references for identifier and a description.

arturro · Accepted Answer · 2017-01-09 21:12:55Z

0

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);

answered Jan 9, 2017 at 21:12

arturro

1,6061 gold badge10 silver badges13 bronze badges

1 Comment

Jorge Over a year ago

Perfect, i just saw an image with joins explication. Thanks!

Collectives™ on Stack Overflow

Postgresql - Conditional Join if data exist

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related