SQL Select with broken table relationship

Question

I have a table structure on Postgree like this:

Table1
    pktab1
    somedate
    valor
Table2
    pktab2
    fktab1
    valor
Table3
    pktab3
    fktab1
    fktab4
    valor
Table4
    pktab4
    condition

They´re related through PK & FK keys of each table.

The field table1.value controls whether I have values on table2 or table3.

table1.value = 1 | table2.value = NULL / table3.valeu = XX

table1.value = 2 | table2.value = XX / table3.valeu = NULL

I need a resulting table with 3 columns: table1.date, table2.value and table3.value

I´m trying to do a select using the following syntax:

select 
    table1.somedate,
    table2.valor,
    table3.valor
from table1
   inner join table2 on (table2.fktab1 = table1.pktab1)
   inner join table3 on (table1.pktab1 = table3.fktab1)
   inner join table4 on (table3.fktab4 = table4.pktab4)
where 
      (table1.valor = 2 and table4.condition = 1)
       or 
      (table1.valor = 1)

But it only returns NULL values. I also tried using:

where   
    (table1.value = 1 and table4.condition = 1)
    or
    (table1.value = 2 and table4.condition IS NULL)

But also didn´t work.

If I remove Table2 from the query, everthing works fine. It seems to me that in some records the where clause "table4.condition = XX" is unreacheable, causing the whole query to be NULL, but I just don´t know how to make a turn around this.

Anyway to solve it?

Try using a LEFT JOIN instead of the INNER JOIN for table2. Sounds like you don't have corresponding data in that table. — sgeddes
Impossible to say for sure without knowing your schema but @sgeddes is likely to be right. All that filters and joins cannot find a match meaning there something wrong in your logic — jean
@sgeddes Thanks! It worked! In my case, I had to left join all tables then It worked fine. — Rafael Leonardi

Rafael Leonardi Rafael Leonardi · Accepted Answer · 2015-03-03T11:38:00

1

votes

As @sgeddes suggested, I used LEFT JOIN then the problem was solved.

SQL Select with broken table relationship

1 Answers