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