Re: HELP with a query with blank fields - Mailing list pgsql-general

On Tue, 27 May 2008, J. Manuel Velasco wrote:

> Hello,
>
> This is the current query I have:
>
> SELECT dominis.nom, dominis.extensio, dominis.creat, dominis.expira,
> titulars.first_name, titulars.last_name, contactes_admin_tec.first_name,
> contactes_admin_tec.last_name, dns1.nom, dns2.nom, dominis.redirec,
> contactes_fac.nom, grups.nom FROM dominis, contactes_fac, dns as dns1,
> dns as dns2, titulars, contactes_admin_tec, grups WHERE
> dominis.id_c_f=contactes_fac.id AND dominis.id_dns1=dns1.id AND
> dominis.id_dns2=dns2.id AND dominis.id_titular=titulars.id AND
> dominis.id_c_a=contactes_admin_tec.id AND contactes_fac.id_grup=grups.id
> AND dominis.id_c_f = 724
>
> The problem is that are registers that has not dominis.id_dns2 value and
> then they are not extracted. I need to show also these ones.
>
> I try playing with inner join, left join,... but I get this error:
>
> ERROR:  referencia invalidad a una entrada de clausula FROM para la
> tabla "dominis"
> LINE 9: ON dominis.id_dns2 = dns2.id
>            ^
> HINT:  Hay una entrada para la tabla "dominis", pero este no puede ser
> referenciado desde esta parte de la consulta.
>
> Free translation: Invalid reference in FROM clausule. There is an entry
> in table dominis but it can't referenced from this part of the query.

This usually means that you've mixed up the conversion to SQL join syntax.
Since you aren't showing the exact query after conversion, it's hard to
say exactly, but usually this comes up if you do something like
 FROM a, b LEFT JOIN c ON (a.col = c.col)
because that's effectively
 a cross join (b left join c on (a.col = c.col))
not
 (a cross join b) left join c on (a.col = c.col)

So, if you haven't converted entirely from comma separated from entries to
SQL join syntax, you might want to try that first. So, something like:
 SELECT dominis.nom, dominis.extensio, dominis.creat, dominis.expira,
  titulars.first_name, titulars.last_name, contactes_admin_tec.first_name,
  contactes_admin_tec.last_name, dns1.nom, dns2.nom, dominis.redirec,
  contactes_fac.nom, grups.nom
 FROM dominis JOIN contactes_fac ON (dominis.id_c_f = contactes_fac.id)
              JOIN titulars ON (dominis.id_titular = titulars.id)
              JOIN contactes_admin_tec ON (dominis.id_c_a =
contactes_admin_tec.id)
              JOIN grups ON (contactes_fac.id_grup = grups.id)
              JOIN dns as dns1 ON (dominis.id_dns1 = dns1.id)
         LEFT JOIN dns as dns2 ON (dominis.id_dns2 = dns2.id)
 WHERE
  dominis.id_c_f = 724