sql - Filter the rows from one table based on matching columns values in another table with multiple conditions

Question

I have table A and table B where I need to filter out the rows from table A with matching values on some columns from table B.

Below are some conditions to check and filter:

 1. A.c1 = B.c1, A.c2 = B.c2, A.c3 = B.c3, A.c4 = B.c4, A.c5 = B.c5
 2. A.c1 = 'ALL', A.c2 = B.c2, A.c3 = B.c3, A.c4 = B.c4, A.c5 = B.c5
 3. A.c1 = 'ALL', A.c2 = 'ALL', A.c3 = B.c3, A.c4 = B.c4, A.c5 = B.c5
 4. A.c1 = 'ALL', A.c2 = 'ALL', A.c3 = 'ALL', A.c4 = B.c4, A.c5 = B.c5
 5. A.c1 = 'ALL', A.c2 = B.c2, A.c3 = 'ALL', A.c4 = B.c4, A.c5 = B.c5
 6. ....

 32. A.c1 = 'ALL', A.c2 = 'ALL', A.c3 = 'ALL', A.c4 = 'ALL', A.c5 = 'ALL'

when A.cX is 'ALL' we match on other conditions of Table B and filter the rows.

And so on with all combinations that are 32.

What would be an efficient way to write SQL query or data frame operations.
Example Filtered output

question from:https://stackoverflow.com/questions/66068566/filter-the-rows-from-one-table-based-on-matching-columns-values-in-another-table

Answer 1

Waitting for answers