1

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
)
  • I'll add another interpretation in my answer below – Lennart Dec 30 '14 at 14:52
3

@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.

| improve this answer | |
  • I messed up with my question (was 4AM while I was writing) so I add some extra information, can you take a look and reformulate your answer? Also if you need I can provide you with some kind of data for testing purposes – ReynierPM Dec 30 '14 at 14:26
  • @ReynierPM I have updated the answer, giving you both potential interpretations of "the opposite", choose the one you want. – jynus Dec 31 '14 at 10:46
  • I'm still getting problem with query not returning the right result, can you take a look to my latest edit? – ReynierPM Jan 4 '15 at 23:41
2

I assume you mean not associated to a certain neg.fabricante_distribuidor_id and neg.producto_solicitud_id = 1. The easiest solution is to negate the predicate in the where clause:

WHERE NOT ( neg.fabricante_distribuidor_id = 1 AND 
            neg.producto_solicitud_id = 1 )

You may prefer:

WHERE neg.fabricante_distribuidor_id <> 1 OR 
      neg.producto_solicitud_id <> 1

instead

Edit: Is this close to what you want?

SELECT ps.id, ps.nombre
FROM nomencladores.pais ps 
WHERE NOT EXISTS (
    SELECT 1
    FROM negocio.fabricante_producto_solicitud neg 
    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
);
| improve this answer | |
  • I've tried your query and get empty results – ReynierPM Dec 30 '14 at 14:59
  • Should be: negocio.fabricante_producto_solicitud – Lennart Dec 30 '14 at 15:02
  • yep, I've fixed that part but your query return no records meaning empty results – ReynierPM Dec 30 '14 at 15:05
  • Could you please provide sample data and expected result. – Lennart Dec 30 '14 at 15:09
  • Sample data is already on the main post and expected result should be any pais.id, pais.nombre where pais.id is different from 6, 1, 2, 3, 5, 8 because this are the ones on pais_fabricante_producto_solicitud table. If you run my first query you'll get this ouput – ReynierPM Dec 30 '14 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.