How to return null value if the query has no corresponding value?

Question

Hi

i've a query

select c.name as companyname, u.name,u.email,u.role,a.date 
from useraccount u, company c, audittrial a 
where 
    u.status='active' 
    and u.companyid=c.id 
    and 
    (
        u.companyid=a.companyID 
        and a.activity like 'User activated%' 
        and a.email=u.email
    ) 
order by u.companyid desc 
limit 10

So if the following part doesnt't satisfy,

(
    u.companyid=a.companyID 
    and a.activity like 'User activated%' 
    and a.email=u.email
)

no rows will be returned..

but i want to return the result of the following query

select c.name as companyname, u.name,u.email,u.role,a.date 
from useraccount u, company c, audittrial a 
where 
    u.status='active' 
    and u.companyid=c.id 
order by u.companyid desc 
limit 10

but to add that, i should return the date if available and return null value if date is not available..

how can i do this?

Answer 1

Multiple tables combined with a where clause are effectively an inner join:

select *  from table1, table2 where table1.id = table2.id

is the same as

select *  from table1 inner join table2 on table1.id = table2.id

To make the audit trail optional, change it to a left join:

select  ...
from    useraccount u
join    company c
on      u.companyid = c.id
left join
        audittrial a
on      a.activity like 'User activated%' 
        and a.email = u.email
order by
        u.companyid desc 
limit   10

By the way, I think the proper spelling is audit tr ai l.

Answer 2

SELECT c.name as companyname, u.name,u.email,u.role,
       IF(a.date!='', a.date, null) AS audit_date

FROM "rest of query"