Обсуждение: HELP with a query with blank fields

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.

I also tried changing
dominis.id_dns2=dns2.id by (dominis.id_dns2=dns2.id or dominis.id_dns2
is null)
but it's wrong, i get more than one record of the field from the
register there is an empty value in id_dns2 field.

Can anybody help to achieve the goal? I really will appreciate.

Thanks in advance.

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