mardi 14 juin 2016

SQL Query - Indirect joining of two tables

I have two tables like the following

Table1

COL1    COL2    COL3
 A       10     ABC
 A       11     ABC
 A       1      DEF
 A       2      DEF
 B       10     ABC
 B       11     ABC
 B       1      DEF
 C       3      DEF
 C       12     ABC
 C       21     GHI

Table2

COL1   GHI  ABC DEF
 A1    21   10  1
 A2    21   12  1
 A3    21   10  1
 A4    23   10  1
 A5    25   11  3
 A6    21   14  3
 A7    25   11  1
 A8    23   10  1
 A9    29   10  2
 A10   21   12  3

I have created another temporary table that returns all the distinct values from tbl1.col1

The values of col3 in tbl1 are columns in tbl2, which are populated by some values.

What I need is for each of these distinct values of table1.column1, (A, B, C) in this case, return a combination of table2.column1 and table1.column1 such that

  • the ABC value of table2.column1 matches any of the ABC value of the "group" from table1,
  • AND the DEF value of table2.column1 matches any of the DEF value of the "group" from table1,
  • AND IF THE GROUP CONTAINS GHI VALUES, the GHI value of table2.column1 matches any of the GHI value of the "group" from table1

So, I would need something like the following

Output Table

Table2.COL1   Table1.Col1
     A1            A
     A3            A
     A4            A
     A7            A
     A8            A
     A9            A
     A1            B
     A3            B
     A4            B
     A7            B
     A8            B          
     A10           C

I tried something like this, but Im not sure if this is the right way of approaching

select table2.col1, temp_distinct_table.column1 
      from table2, temp_distinct_table
      where table2.def IN (SELECT col2 
                           FROM table1 
                           WHERE table1.col1 = temp_distinct_table.col1 
                                 AND table1.col3 = 'DEF')
      AND table2.abc IN (SELECT col2 
                         FROM table1 
                         WHERE table1.col1 = temp_distinct_table.col1 
                                 AND table1.col3 = 'ABC')
      AND (
          table2.ghi IN (SELECT col2 
                          FROM table1 
                          WHERE table1.col1 = temp_distinct_table.col1  
                                 AND table1.col3 = 'GHI') 
         OR NOT EXISTS (SELECT col2 
                     FROM table1 
                     WHERE table1.col1 = temp_distinct_table.col1 
                       AND table1.col3 = 'GHI')
          )

where temp_distinct_table contains of all the distinct values from table1.col1

Could someone guide me on the matter?

Aucun commentaire:

Enregistrer un commentaire