Thread: Linking against null-fields.

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);

However, the only reason I have this query set up as this is to get the name 
of the driver easily, if one is set.

How can this be helped? Should I just get the driver ID as a field, and then 
do a second query on that?

Thanks,

Alexander Deruwe

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

> SELECT f.ID, d.NAME FROM FILE f, DRIVER d WHERE (d.ID = f.DRIVER);
>
> However, the only reason I have this query set up as this is to get the
name
> of the driver easily, if one is set.
>
> How can this be helped? Should I just get the driver ID as a field, and
then
> do a second query on that?

I think this might work:
select f.ID, d.NAME from FILE f LEFT OUTER JOIN  DRIVER d ON(d.ID=f.DRIVER);



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com