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