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, Jan 18 '19 at 16:09
@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, Jan 18 '19 at 16:15
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, Jan 18 '19 at 16:24
@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, Jan 18 '19 at 16:41
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
and
tags or indented 4 spaces. I fixed one data set for you. — Sentinel, Jan 18 '19 at 16:55
@Sentinel Thanks for doing that. GH and IJ are values from Col3 in table B. This is how they'll come...distinct(B.Col3) where B.Col3<>'CD'. I know this is confusing but thinking if its doable. — django-unchained, Jan 18 '19 at 18:40

Sentinel · Accepted Answer · 2019-01-18T23:24:07.527

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 |

score 0 · Answer 2 · edited Jan 18 '19 at 20:22

0

Result for I:-

;with cte1 As(select a.col1,a.col2 from A a left join B b on a.col1 =b.col2 and a.col2=b.col2 where b.col4 = 'I'),cte2 As(select b.col3,b.col4,b.col5 from from A a left join B b on a.col1 =b.col2 and a.col2=b.col2 where b.col4 <> 'I')

Result for E:-

select a.col1,a.col2,b.col3,b.col4,b.col5 from cte1 a cross join cte2 b 
;with cte1 As(select a.col1,a.col2 from A a left join B b on a.col1 =b.col2 and a.col2=b.col2 where b.col4 = 'E'),cte2 As(select b.col3,b.col4,b.col5 from from A a left join B b on a.col1 =b.col2 and a.col2=b.col2 where b.col4 <> 'E')
select a.col1,a.col2,b.col3,b.col4,b.col5 from cte1 a cross join cte2 b

edited Jan 18 '19 at 20:22

answered Jan 18 '19 at 17:10

maddy

50
1
1
10

Your query returns duplicates. – django-unchained Jan 18 '19 at 18:35
just edited the query by adding group by clause in the end. – maddy Jan 18 '19 at 18:45
Thanks! But if you see the expected result set for 'E' in my question above, don't think your query will return that. I want to return all the combinations of Col1,Col2,Col3 with distinct(B.Col3) where B.Col3 <> (The value in Col3 for E cases) – django-unchained Jan 18 '19 at 19:01
I have tested with dummy tables .its working.could you please see if it is working for you if not I'll dig more into it. – maddy Jan 18 '19 at 19:09
I tested this one not working. Another thing is it returns all the combinations from A and B since its a cross join. Which is not correct because I need Col3 value only if its present in table B. Your query is returning cartesian product of two tables. – django-unchained Jan 18 '19 at 19:13
Also, can you please add your code in code snippet. Select text and press CTRL+K to toggle indenting as code. – django-unchained Jan 18 '19 at 19:14

Bob Dubke · Answer 3 · 2019-01-18T16:23:23.877

select c.col1, c.col2 

from 
(select a.col1, a.col2, b.col3 from  a inner join table b on a.id = b.id
where "condition" ) c

where c.col1 = "condition"

This is the script. The explanation is:

Inside the () i wrote the first select. There, you will do the select with your joins and your conditions. At the end of the select i wrote "c" which is the name of the table generated from the sub-select. Then, you'll select some values from the generated table and filter them with a where that will act on the results generated by the table created with the sub-select

EDIT: I used your question's names to make it easier

How to return values from same table?

3 Answers3