2

Let's say I have the following two tables

Table 1

Column1 Column2 Column3
   1       A      ABC
   1       B      DEF
   1       C      DEF
   1       D      GHI
   1       E      GHI
   2       A1     ABC
   2       B1     DEF
   2       C1     DEF
   2       D1     GHI


Table 2

Column1   DEF     GHI
   X       B       D
   X       C       D
   X       C       E
   X       G       D
   Z       B       D

Two values of column 3 of table 1 are columns in Table 2, and these columns are populated with the data in table1.column2.

Now, I need to write an SQL query such that for each group in table 1 (groups based on table1.column1), I am able to get all the values in table 2 which consists of all the DEF values of the group in table2.column2 and all of the GHI values in table2.column3

For example, for the given table, my expected output should be only X. Because X has both B and C in column DEF and has both D and E in column GHI. Z, on the other hand, does not contain C in the DEF column.

Can someone guide me on how to proceed and how should I go about it?

aakashgupta.0205
  • 647
  • 1
  • 8
  • 23
  • Can you please tag with the relevant database (Oracle I'm guessing)? `PIVOT` is what you're looking for. – Liesel May 31 '16 at 10:39
  • How would PIVOT help me in this case? – aakashgupta.0205 May 31 '16 at 10:45
  • Sorry, misread your question - thought you were trying to get t2 from t1 – Liesel May 31 '16 at 11:12
  • It seems likely that this is a bad design. Pivoting is something to be done to format a relation for consumption after you are done calculating. The solution in cases like this basically involves undoing some pivot that was done with data from relations that should have been kept around and used instead here. Eg table 2's example value is a pivot from a union of joins. – philipxy Jun 06 '16 at 05:22

1 Answers1

2

This seems complicated. I think you can do a join from table 2 back to table 1 twice -- once for each column. This gets all the matches for each table1.col1.

Then, the query can aggregate the results and check if all the matches are complete. Because this is occurring along two dimensions, the having clause needs to use count(distinct):

select t2.col1, t1.col1
from table2 t2 join
     table1 tdef
     on tdef.col3 = 'DEF' and tdef.col2 = t2.def join
     table1 tghi
     on tghi.col3 = 'GHI' and tghi.col2 = t2.ghi and tghi.col1 = tdef.col1 join
     (select sum(case when t1.col3 = 'DEF' then 1 else 0 end) as cnt_def,
             sum(case when t1.col3 = 'GHI' then 1 else 0 end) as cnt_ghi
      from table1 t1
     ) tt1
     on tt1.col1 = tdef.col1
group by t2.col1
having count(distinct tdef.col2) = tt1.cnt_def and
       count(distinct tghi.col2) = tt1.cnt_ghi;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786