From: Martín Marqués <martin@bugs.unl.edu.ar>
To: pgsql sql Mailing List <pgsql-sql@postgresql.org>
Subject: views and null bothering
Date: Mon, 6 Aug 2001 20:36:23 -0300
Message-ID: <01080620362304.00347@bugs> (raw)
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
-----------------------------------------------------------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: martin@bugs.unl.edu.ar
Subject: Re: views and null bothering
In-Reply-To: <01080620362304.00347@bugs>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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