Get all (non repeated) distribuidores with their paises given a producto_solicitud I have this schema:



And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way:

SELECT
    nomencladores.fabricante_distribuidor.*,
  nomencladores.pais.*
FROM
    nomencladores.fabricante_distribuidor,
    nomencladores.pais
LEFT JOIN negocio.distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor. ID
)
LEFT JOIN nomencladores.pais_distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud. ID = nomencladores.pais_distribuidor_producto_solicitud.distribuidor_producto_solicitud_id
)
WHERE
    negocio.distribuidor_producto_solicitud.producto_solicitud_id = 1


But get this error:


[Err] ERROR:  invalid reference to FROM-clause entry for table "fabricante_distribuidor"
LINE 8:&gt; ...r_producto_solicitud.fabricante_distribuidor_id = nomenclado...
                                 ^
HINT:  There is an entry for table "fabricante_distribuidor", but it cannot be referenced from this part of the query.



I've tried also this one:

SELECT
    nomencladores.fabricante_distribuidor.*
FROM
    nomencladores.fabricante_distribuidor
JOIN negocio.distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor. ID
)
JOIN nomencladores.pais_distribuidor_producto_solicitud ON (
    negocio.distribuidor_producto_solicitud. ID = nomencladores.pais_distribuidor_producto_solicitud.distribuidor_producto_solicitud_id
)
WHERE
    negocio.distribuidor_producto_solicitud.producto_solicitud_id = 1


But this one return repeated results, any help?

I'm using PostgreSQL but assume that will be pretty much closed to any SQL statement.
2nd query:
Of course you get duplicate rows. 1 row per producto each fabricante is connected to - multiplied with the number of rows in pais each combination is connected to.

1st query:
An explicit JOIN binds before (groups of) comma-separated items in the FROM list. This is why you cannot reference fabricante in the JOIN condition between pais and distribuidor_producto_solicitud - that happens before the result of this operation is joined to fabricante. Per documentation:


  In the absence of parentheses, JOINs nest left-to-right. In any case
  JOIN binds more tightly than the commas separating FROM-list items.


Bold emphasis mine.

The unconditional CROSS JOIN (comma-notation) between fabricante and the rest is a mistake to begin with. You probably want:

SELECT DISTINCT f.*, p.*
FROM   negocio.distribuidor_producto_solicitud d
JOIN   nomencladores.fabricante_distribuidor   f ON f.id = d.fabricante_distribuidor_id
LEFT   JOIN nomencladores.pais_distribuidor_producto_solicitud pd
                                                 ON pd.distribuidor_producto_solicitud_id = d.id
JOIN   nomencladores.pais                      p ON p.id = pd.pais.id
WHERE  d.producto_solicitud_id = 1;


Gives you one row for every combination of (fabricante, pais). The LEFT JOIN is there to include fabricantes that have no pais attached.

You need the DISTINCT, because there can be any numbers of rows in distributor for each fabricante.

With your verbose naming convention I suggest to use table aliases to make the query more readable.