Combine the results of two queries into one

130
April 10, 2018, at 09:10 AM

I am trying to make the following query in MySQL:

Find the IDs of the suppliers that supply all the parts in red or green.

The structure of the tables would be this:

proveedores(ID: int (PK), nombre: nvarchar, direccion: nvarchar)
partes(ID: int (PK), nombre: nvarchar, color: nvarchar)
catalogo(proveedores_ID: int (FK), partes_ID: int (FK), costo: double)

And I have managed to make a querys that allows me to see the IDs of the suppliers that distribute all the parts of red or green as the case may be.

The query using "red" or "Green" is this:

SELECT DISTINCT C.proveedores_ID FROM fabrica.catalogo C
WHERE NOT EXISTS (
                  SELECT P.ID FROM fabrica.partes P
                  WHERE P.color = "red" AND ( NOT EXISTS (
                                                           SELECT C1.proveedores_ID FROM fabrica.catalogo C1
                                                           WHERE C1.proveedores_ID = C.proveedores_ID AND C1.partes_ID = P.ID
                                                         )
                                            )
                 ); 

but I could not combine it to generate a query that allows me to return the previously given requirement. I would appreciate if someone can help me achieve the expected result.

Answer 1

You can join two NOT EXISTS conditions with an OR to fetch providers for whom there aren't red parts they haven't sold, or there aren't green parts they haven't sold:

SELECT DISTINCT C.proveedores_ID FROM fabrica.catalogo C
WHERE NOT EXISTS (
                  SELECT P.ID FROM fabrica.partes P
                  WHERE (P.color = "red")
                      AND ( NOT EXISTS (
                          SELECT C1.proveedores_ID FROM fabrica.catalogo C1
                          WHERE C1.proveedores_ID = C.proveedores_ID
                              AND C1.partes_ID = P.ID
                          )
                  )
) OR NOT EXISTS (
                  SELECT P.ID FROM fabrica.partes P
                  WHERE (P.color = "green")
                      AND ( NOT EXISTS (
                          SELECT C1.proveedores_ID FROM fabrica.catalogo C1
                          WHERE C1.proveedores_ID = C.proveedores_ID
                              AND C1.partes_ID = P.ID
                          )
                  )
);
Rent Charter Buses Company
READ ALSO
Unable to insert data to MySQL using Python and scrapy pipelines

Unable to insert data to MySQL using Python and scrapy pipelines

I have tried hours to get around this but still cannot make it to work properlyI am using scrapy to scrape data from a website and then trying to insert that into MySQL database

145
Assign value from mysql into dict using python

Assign value from mysql into dict using python

If I am only returning one row from my mysql query and I know the headers can I assign those values via dict in python? I do have some data that is nestedThe data will eventually be returned as a JSON object

177
code Igniter Failover Array

code Igniter Failover Array

I'm not needing help with failover connection, just what might be wrong with this failover array? I have tested the alternate db connection and works greatIssue is when I turn off primary, failover does not connect as it should

139
Setting php generated html table in parts side by side

Setting php generated html table in parts side by side

I made this code to extract data from mysql and bring the results on the pageThe results that the code generated goes down the page

163