CODEWITHSUNDEEP
sqlmysql-workbench
R Ban
R Ban

Reputation: 97

SQL : joins , I tried cross join but it is not working

I would appreciate if someone can help me to get the required output

I have a table as below :

Field1 Field2 Field3 Field4  
1       AB    A123   Electric  
1       AB    B123   Maths  
1       CD    XY1    Science  
1       CD    CYZ    Social  
1       SQ    NA     NA

The output should be like this :

Field1 Field2 Field3 Field4
1       AB    A123   Electric  
1       CD    NA     Electric  
1       AB    B123   Maths  
1       CD    NA     Maths  
1       CD    XY1    Science  
1       AB    NA     Science  
1       CD    CYZ    Social  
1       AB    NA     Social  
1       SQ    NA     Electric  
1       SQ    NA     Math  
1       SQ    NA     Science   
1       SQ    NA     Social

Here Field1 has a common value, based on values of Field2 and Field 4, we need to check if Field2 and Field4 has an entry in other Field2 like AB and Electric, does it has entry for Field2 I mean for CD and SQ same Field4 if not then create an entry.
It's like if we have all the unique values in field4 and then each and every entry should be present all the values in Field2. if it does not have , then it should specify does not exist or NA.

SQ does not have any entry so four rows should be created. Kindly advise.

Upvotes: 1

Views: 131

Answers (2)

forpas
forpas

Reputation: 164089

Join the distinct values of field2 and field4 and left join the table:

select 
  f2.field1, 
  f2.field2,
  coalesce(t.field3, 'NA') field3,
  f4.field4
from (select distinct field1, field2 from tablename) f2
inner join (select distinct field1, field4 from tablename where field4 <> 'NA') f4
on f4.field1 = f2.field1
left join tablename t
on t.field1 = f2.field1 and t.field2 = f2.field2 and t.field4 = f4.field4
order by f2.field1, f2.field2

See the demo.
Results:

| field1 | field2 | field3 | field4   |
| ------ | ------ | ------ | -------- |
| 1      | AB     | NA     | Science  |
| 1      | AB     | NA     | Social   |
| 1      | AB     | A123   | Electric |
| 1      | AB     | B123   | Maths    |
| 1      | CD     | XY1    | Science  |
| 1      | CD     | CYZ    | Social   |
| 1      | CD     | NA     | Electric |
| 1      | CD     | NA     | Maths    |
| 1      | SQ     | NA     | Electric |
| 1      | SQ     | NA     | Maths    |
| 1      | SQ     | NA     | Science  |
| 1      | SQ     | NA     | Social   |

Upvotes: 2

VBoka
VBoka

Reputation: 9083

In this example you have a table called a

with cte as (
    select Field2 as f
    from a
    union all
    select Field4 as f
    from a    
)
select distinct 1 Field1
       , ct1.f Field2
       , case when (ct1.f, a.Field3, ct2.f) in (select distinct Field2, Field3, Field4
                                                from a) 
              then a.Field3
              else 'NA'
         end Field3
       , ct2.f Field4
    from cte ct1 
        cross join cte ct2
    on ct1.f <> ct2.f
    left join a 
    on ct1.f = a.Field2
    and ct2.f = a.Field4
where ct1.f in (select Field2 from a)     
and ct2.f in (select Field4 from a where Field4 <> 'NA')

HERE IS A DEMO

Upvotes: 3

Related Questions