SQL Select statement - multiple tables allow null values

Question

I do realise that problem I am facing is not a rocket science but still, I did not find any information about fixing this.

I have multiple tables in my database (PSQL) I want to create a select query to make a reporting function for my app.

Here is my query:

select 
    s.id, s.name, st.name, p.firstname || ' ' || p.lastname, 
    f.name, f.store_date, bdt.name, bd.comment 
from  
    system s, systemstatus st, role w, person p, file f, 
    documenttype bdt, document bd 
where 
    w.system_id = s.id and 
    p.id = w.person_id and 
    st.id = s.status_id and 
    bd.system_id = s.id and 
    bd.file_id = f.id and 
    bd.type_id = bdt.id and 
    bd.role_id = w.id;

Query works I get 300 rows fully filled with values I am searching for. Problem is that I have about 1000 rows in System Table. It is possible that there is no Person or Document which could be linked with particular System.

I would like to see all rows that are in my System table (I mean about 1000), and when I can't link Person or Document with System I want the field to be null ( now it is not shown at all)

Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 20 years ago) and its use is discouraged — marc_s
– marc_s, Commented Aug 1, 2015 at 7:43

roman · Accepted Answer · 2013-08-29 07:06:29Z

5

Main part of the answer is - you need left outer join.
Additional parts of the answer - use ANSI join syntax and format your queries:

select
    s.id, s.name, st.name, p.firstname || ' ' || p.lastname, f.name,
    f.store_date, bdt.name, bd.comment
from system as s
    left outer join systemstatus as st on st.id= s.status_id
    left outer join role as w on w.system_id = s.id
    left outer join person as p on p.id = w.person_id
    left outer join document as bd on bd.system_id = s.id and bd.role_id = w.id
    left outer join documenttype as bdt on bdt.id = bd.type_id
    left outer join file as f on f.id = bd.file_id

Always remember that somebody will read your code someday (may be it will be future you :) ) - so readability counts!

answered Aug 29, 2013 at 7:06

roman

118k30 gold badges205 silver badges209 bronze badges

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

Comments

athabaska · Accepted Answer · 2013-08-29 07:01:00Z

0

You are connecting your tables in query via "where" clause, which is equal to inner join. Instead you should left join Person and Document tables to System table, e.g.:

select * 
from system s
left join role w on w.system_id = s.id
left join person p on p.id = w.person_id

answered Aug 29, 2013 at 7:01

athabaska

4553 silver badges23 bronze badges

1 Comment

Mithrand1r Over a year ago

I will try this and keep You posted. Thank for advice !

Collectives™ on Stack Overflow

SQL Select statement - multiple tables allow null values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related