Postgresql – Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

optimizationperformancepostgresqlquery-performance

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine.

SELECT DISTINCT nom.pais_id
FROM   negocio.fabricante_producto_solicitud neg
       INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
         ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE  neg.fabricante_distribuidor_id = 1
       AND neg.producto_solicitud_id = 1

But I want to get the opposite, I mean all the pais_id that does not associated to fabricante_distribuidor_id and producto_solicitud_id, how?

NOTE:

I though this will be more easy but I'm getting some problems getting the data I need. Take a look to this piece of my model:

enter image description here

Well based on this (and was my bad, my sincerely apologies) what I need to get is the pais.id, pais.nombre that has not relation with any fabricante_producto_solicitud.fabricante_distribuidor_id having the same input, meaning fabricante_distribuidor_id and producto_solicitud_id, this is the right question!

I've tried this query on my own:

SELECT
    ps.id, ps.nombre
FROM
    nomencladores.pais_fabricante_producto_solicitud nom
LEFT JOIN negocio.fabricante_producto_solicitud neg ON (
    neg. ID = nom.fabricante_producto_solicitud_id
)
LEFT JOIN nomencladores.pais_fabricante_producto_solicitud pfs ON (
    pfs.fabricante_producto_solicitud_id = nom.fabricante_producto_solicitud_id
)
LEFT JOIN nomencladores.pais ps ON (
  ps."id" = pfs.pais_id
)
WHERE
    neg. ID IS NULL
OR NOT (
    neg.fabricante_distribuidor_id = 1
    AND neg.producto_solicitud_id = 1
)

But it's complete wrong since I get repeated results and also I don't get the expected output. Can I have some advice?

NOTE2: The tables on purple belongs to nomencladores schema and the one in orange belongs to negocio schema

Here is some structure & data files to play with, let me know if any have problems downloading

EDIT: Playing with queries

I'm still playing with queries trying to get the result I'm looking for but this, I made based on the ones leave here, is not working, meaning I'm getting all the rows all the time not matter what parameter changes, why?

SELECT
 nomencladores.pais.id as ID,
 nomencladores.pais.nombre as nombre
FROM
 nomencladores.pais
WHERE ID NOT IN(
 SELECT
     nomencladores.pais.id
 FROM
     negocio.fabricante_producto_solicitud
        INNER JOIN nomencladores.pais_fabricante_producto_solicitud ON (negocio.fabricante_producto_solicitud.id = nomencladores.pais_fabricante_producto_solicitud.fabricante_producto_solicitud_id)
        INNER JOIN nomencladores.pais ON (nomencladores.pais_fabricante_producto_solicitud.pais_id = nomencladores.pais.id)
        INNER JOIN nomencladores.fabricante_distribuidor ON (negocio.fabricante_producto_solicitud.fabricante_distribuidor_id = nomencladores.fabricante_distribuidor.id)
 WHERE
     negocio.fabricante_producto_solicitud.producto_solicitud_id = 3
AND
     negocio.fabricante_producto_solicitud.fabricante_distribuidor_id = 37
GROUP BY
     nomencladores.pais.id
)

Best Answer

@Lennart's answer is one way to negate your query, but it only selects países that are associated with other fabricantes_distribuidores. If you want to include países that are not linked with any distribuidor, too, you have to add an outer join (I use a left outer join as per convention):

SELECT DISTINCT nom.pais_id 
FROM nomencladores.pais_fabricante_producto_solicitud nom 
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id ) 
WHERE neg.id IS NULL OR 
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

Check the differences in results between the original query, Lennart suggestion and mine:

mysql> SELECT * FROM nomencladores.pais_fabricante_producto_solicitud;
+----------------------------------+---------+
| fabricante_producto_solicitud_id | pais_id |
+----------------------------------+---------+
|                                1 |       1 |
|                                2 |       2 |
+----------------------------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM negocio.fabricante_producto_solicitud;
+----+----------------------------+-----------------------+
| id | fabricante_distribuidor_id | producto_solicitud_id |
+----+----------------------------+-----------------------+
|  1 |                          1 |                     1 |
|  3 |                          3 |                     3 |
+----+----------------------------+-----------------------+
2 rows in set (0.00 sec)

Your query:

mysql> SELECT DISTINCT nom.pais_id
    -> FROM   negocio.fabricante_producto_solicitud neg
    ->        INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom
    ->          ON ( neg.id = nom.fabricante_producto_solicitud_id )
    -> WHERE  neg.fabricante_distribuidor_id = 1
    ->        AND neg.producto_solicitud_id = 1;
+---------+
| pais_id |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Lennart's:

mysql> SELECT DISTINCT nom.pais_id FROM
    -> negocio.fabricante_producto_solicitud neg
    -> INNER JOIN nomencladores.pais_fabricante_producto_solicitud nom 
    -> ON ( neg.id = nom.fabricante_producto_solicitud_id ) 
    -> WHERE  NOT (neg.fabricante_distribuidor_id = 1 AND
    -> neg.producto_solicitud_id = 1);
Empty set (0.00 sec)

Mine:

mysql> SELECT DISTINCT nom.pais_id 
    -> FROM nomencladores.pais_fabricante_producto_solicitud nom 
    -> LEFT JOIN negocio.fabricante_producto_solicitud neg
    -> ON ( neg.id = nom.fabricante_producto_solicitud_id ) 
    -> WHERE neg.id IS NULL OR 
    -> NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );
+---------+
| pais_id |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

EDIT: You should only use LEFT JOIN for the "negative" of distribuidor, other relations should be read with normal JOINs:

SELECT DISTINCT ps.id, ps.nombre 
FROM nomencladores.pais_fabricante_producto_solicitud nom 
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
JOIN pais ps
ON ( nom.pais_id = ps.id )
WHERE neg.id IS NULL OR 
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

Example results:

+------+--------+
| id   | nombre |
+------+--------+
|    2 | Perú   |
+------+--------+
1 row in set (0.00 sec)

Again: this will select the id and name of every different country on the database that has a solicitud. If you want to select every possible country (no matter if they have solicitudes or not), you have to move the LEFT JOIN to pais and chain it:

SELECT DISTINCT ps.id, ps.nombre
FROM pais ps
LEFT JOIN nomencladores.pais_fabricante_producto_solicitud nom 
ON ( nom.pais_id = ps.id )
LEFT JOIN negocio.fabricante_producto_solicitud neg
ON ( neg.id = nom.fabricante_producto_solicitud_id )
WHERE nom.pais_id IS NULL OR neg.id IS NULL OR
NOT ( neg.fabricante_distribuidor_id = 1 AND neg.producto_solicitud_id = 1 );

On my example, we would get:

+------+---------+
| id   | nombre  |
+------+---------+
|    2 | Perú    |
|    3 | Uruguay |
+------+---------+
2 rows in set (0.00 sec)

Both solutions are technically correct, the confusion on what the definition of "the opposite" is. The first one is countries that have solicitudes but not for that provider, the second is all countries on the database (having solicitudes or not) minus the ones that have solicitudes for that particular provider.

Related Question