Roger <[EMAIL PROTECTED]> writes:
> Select p.name||p1.name||p2.name
> from people as p left join user as u as
> u.uid = p.pid left joun people as p2 on
> u.uid=p2.pid left join people as p3 on
> u.uid=p3.pid;
>
> The problem is that if one of the rows does not have a value, it returns
> and empty string.Now in my report i end up with lots of null columns.
> Please help, how do i return something at least from this query if one
> of the columns has a null value in it.
I think you're asking two different questions here.
Firstly, to replace an empty string with something non-empty, you can use
COALESCE():
SELECT COALESCE(p.name, 'empty') ||
COALESCE(p1.name, 'empty') ||
COALESCE(p2.name, 'empty')
FROM people p
LEFT JOIN user u
ON u.uid = p.pid
LEFT JOIN people p2
ON u.uid=p2.pid
LEFT JOIN people p3
ON u.uid=p3.pid;
What I think you're really asking, though, is about the rows that are NOT
returned because there are null values. To fix that, you're probably looking
for LEFT OUTER JOIN:
SELECT p.name || p1.name || p2.name
FROM people p
LEFT OUTER JOIN user u
ON u.uid = p.pid
LEFT OUTER JOIN people p2
ON u.uid=p2.pid
LEFT OUTER JOIN people p3
ON u.uid=p3.pid;
Derrell
ps. if you copy/paste the query from your source instead of retyping it,
you'll end up with many fewer typos, and will make it easier for people to
help you. Your query was full of typos.
