pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: Linking against null-fields.
3+ messages / 3 participants
[nested] [flat]

* Re: Linking against null-fields.
@ 2001-10-09 12:21 david@netventures.com.au
  0 siblings, 0 replies; 3+ messages in thread

From: david@netventures.com.au @ 2001-10-09 12:21 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Linking against null-fields.
@ 2001-10-09 13:24 Alexander Deruwe <a@deruwe.be>
  2001-10-09 16:31 ` Re: Linking against null-fields. Stephan Szabo <acroyear_07030@yahoo.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Alexander Deruwe @ 2001-10-09 13:24 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Linking against null-fields.
  2001-10-09 13:24 Linking against null-fields. Alexander Deruwe <a@deruwe.be>
@ 2001-10-09 16:31 ` Stephan Szabo <acroyear_07030@yahoo.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2001-10-09 16:31 UTC (permalink / raw)
  To: a@deruwe.be; pgsql-sql

> 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-10-09 16:31 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-09 12:21 Re: Linking against null-fields. david@netventures.com.au
2001-10-09 13:24 Linking against null-fields. Alexander Deruwe <a@deruwe.be>
2001-10-09 16:31 ` Stephan Szabo <acroyear_07030@yahoo.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox