pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: 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