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:
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
)
Aucun commentaire:
Enregistrer un commentaire