Find items in a table that match a set of conditions in another table

manpreet Best Answer 2 years ago

I have two tables. Table 1 has sets of EXPORT conditions: e.g., SET 1 has FOOD, GARMENT, and TECHNOLOGY, and SET 2 contains GARMENT and TECHNOLOGY.

TABLE 1
SETID       EXPORT
-----------------------
1           FOOD
1           GARMENT
1           TECHNOLOGY
-----------------------
2           GARMENT
2           TECHNOLOGY
-----------------------
3           GARMENT
3           FOOD
-----------------------
4           TECHNOLOGY
(more sets of conditions) ...

Table 2 has a list of countries with different types of exports.

TABLE 2
COUNTRY     EXPORT
-----------------------
GERMANY     AUTO
GERMANY     TECHNOLOGY
-----------------------
ITALY       FOOD              
ITALY       GARMENT 
-----------------------          
CHINA       FOOD
CHINA       GARMENT
CHINA       TECHNOLOGY
CHINA       STEEL
-----------------------
FRANCE      FOOD
FRANCE      GARMENT
-----------------------
(more countries)...

I would like to find countries that satisfy each set of the conditions. A country satisfies a set of conditions if it meets all the conditions in a set (even if it may meet additional conditions) -- For example, CHINA satisfies SET 1 even though it also meets the additional condition STEEL.

The expected output is something like:

SET          Country
--------------------------
1            CHINA
2            CHINA
3            ITALY
3            CHINA
3            FRANCE
4            GERMANY
4            CHINA
...

Here's the query I have. It does the job, but I wonder if there is a way to make it perform better.Currently, when Table 1 has 5000 rows (approx. 600 sets of conditions), and Table 2 has 6 million rows, it takes 15 minutes to get the output.

WITH TEMP AS (  SELECT   SETID, COUNTRY, COUNT(*) AS TOTALCOUNT
                  FROM   #TABLE1 AS T1
                  JOIN   #TABLE2 AS T2 ON T2.EXPORT = T1.EXPORT
              GROUP BY   SETID, COUNTRY
              )
      SELECT  TEMP.SETID, COUNTRY
        FROM  TEMP
        JOIN  (  SELECT  COUNT(*) AS TOTALCOUNT, SETID
                   FROM  #TABLE1
               GROUP BY  SETID
              ) AS TEMP2 ON  TEMP.TOTALCOUNT = TEMP2.TOTALCOUNT AND TEMP.SETID = TEMP2.SETID
    ORDER BY  TEMP.SETID

Code for generating sample tables:

CREATE TABLE #TABLE1 (SETID INT, EXPORT NVARCHAR(MAX))
INSERT INTO #TABLE1(SETID, EXPORT)
VALUES (1, 'FOOD'), (1, 'GARMENT'), (1, 'TECHNOLOGY'), (2, 'GARMENT'),
       (2, 'TECHNOLOGY'), (3, 'GARMENT'), (3, 'FOOD'), (4, 'TECHNOLOGY');

CREATE TABLE #TABLE2 (COUNTRY NVARCHAR(MAX), EXPORT NVARCHAR(MAX))
INSERT INTO #TABLE2(COUNTRY, EXPORT)
VALUES ('GERMANY', 'AUTO'), ('GERMANY', 'TECHNOLOGY'), ('ITALY', 'FOOD'),              
       ('ITALY', 'GARMENT'), ('CHINA', 'FOOD'), ('CHINA', 'GARMENT'
                                                

                                                                                                        REPLY
                                                    
                                                
                                                


                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares

                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp