pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
views and null bothering
3+ messages / 2 participants
[nested] [flat]

* views and null bothering
@ 2001-08-06 23:36  Martín Marqués <martin@bugs.unl.edu.ar>
  0 siblings, 1 reply; 3+ messages in thread

From: Martín Marqués @ 2001-08-06 23:36 UTC (permalink / raw)
  To: pgsql-sql

I have a bunch of tables which I give access through a view. The problem is 
that in the main table there are columns, that are referenced to another 
tables column, that have NULLs.
In the SELECT inside the view's definition I put the join equality, but have 
lots of trouble makeing it put correctly the columns that have NULL values.

This is what I tried:

CREATE VIEW admin_view
SELECT id_curso,car.carrera,titulo,
      car.categoria AS car_categ, 
      categ.categoria, categ.descripcion AS categ_desc
FROM carrera car,resol,inscripcion ins,niveles niv, categ
WHERE resol.carr=car.id_curso AND niv.id_nivel=car.nivel
AND area.id_subarea=car.area AND ins.carrera=car.id_curso
AND categ.id_categ=car.categoria;

But this one doesn't show rows with NULL on column car.categoria.

CREATE VIEW admin_view
SELECT id_curso,car.carrera,titulo,
      car.categoria AS car_categ, 
      categ.categoria, categ.descripcion AS categ_desc
FROM carrera car,resol,inscripcion ins,niveles niv, categ
WHERE resol.carr=car.id_curso AND niv.id_nivel=car.nivel
AND area.id_subarea=car.area AND ins.carrera=car.id_curso
AND (categ.id_categ=car.categoria OR car.categoria IS NULL);

This one repeats rows with NULLs on column car.categoria, one repeate for 
each row element in the categ table, which is not what I need.

The last thing I thought about, but am not sure if it's exactly what I want 
(may do things I doen't want with some queries), is to put a DISTINCT ON 
(id_curso), which would be a solution for the multiple row problem, but I 
would need to add a CASE in the categ.categoria, so that it gives me NULL 
when car.categoria ISNULL.

Am I complicating it to much? Is there an easier way out?

Saludos... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------



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

* Re: views and null bothering
@ 2001-08-07 00:02  Josh Berkus <josh@agliodbs.com>
  parent: Martín Marqués <martin@bugs.unl.edu.ar>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2001-08-07 00:02 UTC (permalink / raw)
  To: Martín Marqués <martin@bugs.unl.edu.ar>; pgsql-sql

Martin,

> I have a bunch of tables which I give access through a view. The
> problem is 
> that in the main table there are columns, that are referenced to
> another 
> tables column, that have NULLs.
> In the SELECT inside the view's definition I put the join equality,
> but have 
> lots of trouble makeing it put correctly the columns that have NULL
> values.

You need to use LEFT OUTER JOIN, supported in Postgres 7.1.x.

See the current postgresql docs, or your favorite ANSI-SQL handbook, for
guidance.

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco



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

* Re: views and null bothering
@ 2001-08-07 21:49  Martín Marqués <martin@bugs.unl.edu.ar>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Martín Marqués @ 2001-08-07 21:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-sql

On Lun 06 Ago 2001 21:02, Josh Berkus wrote:
> Martin,
>
> > I have a bunch of tables which I give access through a view. The
> > problem is
> > that in the main table there are columns, that are referenced to
> > another
> > tables column, that have NULLs.
> > In the SELECT inside the view's definition I put the join equality,
> > but have
> > lots of trouble makeing it put correctly the columns that have NULL
> > values.
>
> You need to use LEFT OUTER JOIN, supported in Postgres 7.1.x.
>
> See the current postgresql docs, or your favorite ANSI-SQL handbook, for
> guidance.

Sorry for the late answer, but... THANKS ALOT! It's the first time I use 
OUTER JOINS, and I think they are great! Thanks Tom for this great feature.

Saludos... :-)

-- 
Cualquiera administra un NT.
Ese es el problema, que cualquiera administre.
-----------------------------------------------------------------
Martin Marques                  |        mmarques@unl.edu.ar
Programador, Administrador      |       Centro de Telematica
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------



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


end of thread, other threads:[~2001-08-07 21:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-08-06 23:36 views and null bothering Martín Marqués <martin@bugs.unl.edu.ar>
2001-08-07 00:02 ` Josh Berkus <josh@agliodbs.com>
2001-08-07 21:49   ` Martín Marqués <martin@bugs.unl.edu.ar>

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