WHERE returning entire table

Question: How do I ignore WHERE param if value = ‘0’ or NULL

One of the columns returned is showing 0 which is causing problems in my query.

The problem I am having is in the p.author value. Sometimes it matches up with a pe.system_id, but sometimes it is 0 or NULL. If p.author is 0. If I comment the and param out, then it returns a row for each entry in docsadm.people pe1 or nothing because no entry = 0

SELECT 
    p.docnumber, p.docname, p.abstract, d.type_id, a.application, 
    pe1.full_name as 'Author', pe.full_name as 'Registered by',
    convert(varchar(10), p.pif_letter_date + 0.4166666,111) as 'Date written', 
    convert(varchar(10), p.creation_date + 0.4166666,111) as 'Registered on', 
    convert(varchar(10), p.last_edit_date + 0.4166666,111) as 'Last edit date', 
    f.pd_filept_no, f.pd_file_name, f.pd_title, sm.pif_marking, s.pd_section_code,
    p.status as 'Availability', p.related, p.kmc_old_docno
FROM 
    docsadm.profile p, docsadm.pd_file_part f, docsadm.people pe, 
    docsadm.people pe1, docsadm.apps a, docsadm.documenttypes d, 
    docsadm.pif_sec_mark sm, docsadm.pd_section s
WHERE 
    p.DOCNUMBER IN ('5451326')
    AND p.pd_file_part = f.system_id
    AND p.typist = pe.system_id
    AND p.author = pe1.system_id --Problem Line
    AND p.application = a.system_id
    AND p.documenttype = d.system_id
    AND f.kmc_ptto_sec_mark = sm.system_id
    AND f.pd_pttosec_link = s.system_id
    AND p.item_type IN ('D','E','M','F')
ORDER BY
    5, 4, 1

Answer

There are a number of ways but the way I prefer is to use the JOIN .. ON syntax and make tables with optional data part of a LEFT OUTER JOIN (LEFT JOIN).

  SELECT p.docnumber,
         p.docname, 
         p.abstract, 
         d.type_id, 
         a.application, 
         pe1.full_name as 'Author', 
         pe.full_name as 'Registered by',
         convert(varchar(10), p.pif_letter_date + 0.4166666,111) as 'Date written', 
         convert(varchar(10), p.creation_date + 0.4166666,111) as 'Registered on', 
         convert(varchar(10), p.last_edit_date + 0.4166666,111) as 'Last edit date', 
         f.pd_filept_no, 
         f.pd_file_name, 
         f.pd_title, 
         sm.pif_marking, 
         s.pd_section_code,
         p.status as 'Availability', 
         p.related, 
         p.kmc_old_docno

    FROM docsadm.profile p
    JOIN docsadm.pd_file_part f
      ON p.pd_file_part = f.system_id
    JOIN docsadm.people pe
      ON p.typist = pe.system_id
    JOIN docsadm.apps a
      ON p.application = a.system_id
    JOIN docsadm.documenttypes d
      ON p.documenttype = d.system_id
    JOIN docsadm.pif_sec_mark sm
      ON f.kmc_ptto_sec_mark = sm.system_id
    JOIN docsadm.pd_section s
      ON f.pd_pttosec_link = s.system_id
    LEFT
    JOIN docsadm.people pe1
      ON p.author = pe1.system_id --Problem Line

    WHERE p.DOCNUMBER in ('5451326')
      and p.item_type in ('D','E','M','F')
    ORDER by 5, 4, 1