How to return values from same table?

Question

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?

Where do Tables A and B come into play? and where did the records in your third data set come from, they don't exist in your first data set. — Sentinel
@Sentinel Col1 and Col2 are coming from table A and Col3, Col4, and Col5 are coming from table B. The result set here in the first picture is the left join of tables A and B on A.Col1=B.Col1 and A.Col2=B.Col2. — django-unchained
If Table A has COL1, and COL2, and Table B has Col1, Col2, Col3, Col4, and Col5 why do you need Table A? And where did values GH and IJ come from in your third set? and in your original Query is it A LEFT JOIN B? if so what happens when columns from Table B are null? — Sentinel
@Sentinel 1. I need table A because I need all records from A. Table B won't necessarily have all the records for Col1 and Col2 combination. When there is no matching value for A.Col1 and A.Col2 from B, Col3, Col4 and Col5 should be blank. 2. Since Col3 is coming from Table B, GH and IJ are from Table B. The reason is, I want to show all the possible values in Col3 except CD for 'E' cases. 3. Yes, it's a left join. If columns in B are NULL, it'll still return all the records from A. — django-unchained
Since you want to `show all possible values in Col3 except CD for 'E' how do I know what all possible values are? GH and IJ weren't shown in your first dataset, they just magically appeared in your 3rd data set. Perhaps, you should start by showing sample data from table A and B that supports the three datasets shown. Also instead of attaching pictures of your data please add the data as formatted text between the <pre> and </pre> tags or indented 4 spaces. I fixed one data set for you. — Sentinel

Sentinel Sentinel · Accepted Answer · 2019-01-18T22:54:33

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 |

How to return values from same table?

3 Answers