pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReferences NULL field
2+ messages / 2 participants
[nested] [flat]
* References NULL field
@ 2006-04-03 03:43 Paul M Foster <paulf@quillandmouse.com>
2006-04-03 03:57 ` Re: References NULL field Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 2+ messages in thread
From: Paul M Foster @ 2006-04-03 03:43 UTC (permalink / raw)
To: pgsql-sql
This is PostgreSQL 7.4 (long story). I have a table, registrars, which
has, among other things,
note_id integer references notes
The notes table has, among other things,
note_id integer default nextval('notes_seq')
Right now, there's nothing in the notes table, and all the records in
the registrars table have null for their note_id field.
I want to do a query (in PHP, FWIW) that returns all the fields in
registrars, and the contents of any relevant notes records, or null for
those fields if there is no corresponding record in the notes table. But
when I do
SELECT * FROM registrars, notes WHERE regname = 'blah'
no matter that I put after the 'blah' (or nothing), I get no results. As
soon as I add notes into the tables being queried, I get nothing.
There's some special way I need to shape this query, but I don't know
what it is. Any help would be appreciated.
--
Paul M. Foster
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: References NULL field
2006-04-03 03:43 References NULL field Paul M Foster <paulf@quillandmouse.com>
@ 2006-04-03 03:57 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Fuhr @ 2006-04-03 03:57 UTC (permalink / raw)
To: Paul M Foster <paulf@quillandmouse.com>; +Cc: pgsql-sql
On Sun, Apr 02, 2006 at 11:43:34PM -0400, Paul M Foster wrote:
> I want to do a query (in PHP, FWIW) that returns all the fields in
> registrars, and the contents of any relevant notes records, or null for
> those fields if there is no corresponding record in the notes table. But
> when I do
>
> SELECT * FROM registrars, notes WHERE regname = 'blah'
>
> no matter that I put after the 'blah' (or nothing), I get no results. As
> soon as I add notes into the tables being queried, I get nothing.
The above query does a cross (cartesian) join of registrars and
notes. Since notes is empty the join result is empty. Try an
outer join:
SELECT *
FROM registrars LEFT OUTER JOIN notes USING (note_id)
WHERE regname = 'blah';
--
Michael Fuhr
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-04-03 03:57 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-03 03:43 References NULL field Paul M Foster <paulf@quillandmouse.com>
2006-04-03 03:57 ` Michael Fuhr <mike@fuhr.org>
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