Re: Linking against null-fields.

Hi Alex,
For this you need an outer join (Made easy as of postgresql 7.1)

Alexander Deruwe Wrote:
> Hey all,
> 
> I'm sorry if this question came up before, I couldn't find it in the
> archives..
> 
> Suppose I have two tables:
> 
> table File: contains alot of fields + 'driver' field, which refers to
> another
> table. I did not 'reference' it when creating the database because
> null-values have to be possible for this field.
> 
> Now, if in this File-table the field 'driver' is not filled in, the row
> will
> not be included in a query such as this one:
> 
> SELECT f.ID, d.NAME FROM FILE f, DRIVER d WHERE (d.ID = f.DRIVER);

In Postgresql 7.1 You would do this as:

SELECT f.ID, d.NAME
FROM FILE f
LEFT OUTER JOIN DRIVER d ON d.ID = f.DRIVER

This join will fill in the missing rows from DRIVER  with NULL values.

Eg:
FILE:
ID    DRIVER
1    NULL
2    1
3    4

DRIVER:
ID    NAME
1    broken.dll
2    foo.zip


SELECT f.ID, d.NAME
FROM FILE f
LEFT OUTER JOIN DRIVER d ON d.ID = f.DRIVER;
ID    NAME
1    NULL
2    broken.dll
3    NULL

--
David Stanaway