Received: from bugs.unl.edu.ar (bugs.unl.edu.ar [168.96.132.208]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f76Nat684677 for ; Mon, 6 Aug 2001 19:36:56 -0400 (EDT) (envelope-from martin@bugs.unl.edu.ar) Received: from localhost (localhost [127.0.0.1]) by bugs.unl.edu.ar (Postfix) with ESMTP id 4002F2AB43 for ; Mon, 6 Aug 2001 20:36:27 -0300 (GMT) Received: by bugs.unl.edu.ar (Postfix, from userid 1004) id 9437E2AB38; Mon, 6 Aug 2001 20:36:24 -0300 (GMT) Content-Type: text/plain; charset="iso-8859-1" From: =?iso-8859-1?q?Mart=EDn=20Marqu=E9s?= To: pgsql sql Mailing List Subject: views and null bothering Date: Mon, 6 Aug 2001 20:36:23 -0300 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01080620362304.00347@bugs> Content-Transfer-Encoding: 8bit X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200108/76 X-Sequence-Number: 4297 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 -----------------------------------------------------------------