How to return values from same table?

I've two tables A and B. I want to return all records from A and only matching from B. I can use left join for this. But after joining, I want to return records based on a flag in the same table.

Table A:

| Col1 | Col2 |
|------|------|
| 123  |  12  |
| 456  |  34  |
| 789  |  56  |

Table B:

| Col1 | Col2 | Col3 | Col4 | Col5 |
|------|------|------|------|------|
| 123  |  12  | NULL |  I   |  1   |
| 456  |  34  | NULL |  E   |  1   |
| 111  |  98  | NULL |  I   |  1   |
| 222  |  99  | NULL |  E   |  1   |
| 123  |  12  |  AB  | NULL |  2   |
| 456  |  34  |  CD  | NULL |  2   |
| 123  |  12  |  EF  | NULL |  2   |
| 111  |  98  |  GH  | NULL |  2   |
| 222  |  99  |  IJ  | NULL |  2   |

After left joining A and B this how the result will look like:

| Col1 | Col2 | Col3 | Col4 | Col5 |
|------|------|------|------|------|
| 123  |  12  | NULL |  I   |  1   |
| 456  |  34  | NULL |  E   |  1   |
| 123  |  12  |  AB  | NULL |  2   |
| 456  |  34  |  CD  | NULL |  2   |
| 123  |  12  |  EF  | NULL |  2   |
| 789  |  56  | NULL | NULL | NULL |

1 and 2 values in Col5 tells if Col4 should be populated or Col3. 1 for Col4 and 2 for Col3.

I want to return all the records for 'I'(but excluding the record which has 'I') in Col4 which will look like this:

| Col1 | Col2 | Col3 |   Col4 | Col5 |
|------|------|------|--------|------|
|  123 |   12 |   AB | (null) |    2 |
|  123 |   12 |   EF | (null) |    2 |

I also want to return records for 'E' (again excluding the record which has 'E') in col4 but for all the values other than one in Col3. In this case CD. Which would look like this:

| Col1 | Col2 | Col3 |   Col4 | Col5 |
|------|------|------|--------|------|
|  456 |   34 |   AB | (null) |    2 |
|  456 |   34 |   EF | (null) |    2 |
|  456 |   34 |   GH | (null) |    2 |
|  456 |   34 |   IJ | (null) |    2 |

Can someone suggest how to handle this in SQL?

Solution

Ok I believe the following two queries achieve your desired results. You can see all the sample code via the following SQL Fiddle.

Existence Rule:

select A.*
     , B.Col3
     , B.Col4
     , B.Col5
  from TableA A
  JOIN TableB B
    on A.Col1 = B.Col1
   and A.Col2 = B.Col2
   and B.Col5 = 2
 where exists (select 1 from TableB C
                where C.col1 = B.col1 and C.col2 = B.col2
                  and c.col4 = 'I' AND C.col5 = 1)

Results:

| Col1 | Col2 | Col3 |   Col4 | Col5 |
|------|------|------|--------|------|
|  123 |   12 |   AB | (null) |    2 |
|  123 |   12 |   EF | (null) |    2 |

Exclusion Rule:

select A.*
     , B.Col3
     , B.Col4
     , B.Col5
  from TableA A
 CROSS JOIN TableB B
 where b.col5 = 2
   and exists (select 1 from TableB C
                where C.col1 = a.col1 and C.col2 = a.col2
                  and c.col4 = 'E' AND C.col5 = 1)
   and b.col3 not in (select col3 from TableB b
                       where b.col1 = a.col1 and b.col2 = a.col2 and b.col5 = 2)

Results:

| Col1 | Col2 | Col3 |   Col4 | Col5 |
|------|------|------|--------|------|
|  456 |   34 |   AB | (null) |    2 |
|  456 |   34 |   EF | (null) |    2 |
|  456 |   34 |   GH | (null) |    2 |
|  456 |   34 |   IJ | (null) |    2 |