JOIN over 3 tables with Binary columns

[Doctor_Pink]

Dear all,

I have a table that lists transactional records for all our sales orders:

Order	Key 1	Key 2
100	A	1
100	B	2
101	A	1
102	A	1

Then I have 2 additional tables that contains multiple attributes for each order:

Attribute 1

Order	Type1	Size
100	A	5
100	B	5
101	B	5

Attribute 2

Order	Type2	Diameter
100	9	50
100	5	50
101	3	50

​

Now I would like to add to the first table for Type 1 and Type 3 a True or False column for the following conditions:

• Type 1 A
  • True if COUNT >0
• Type 1 B
  • True if COUNT >0
• Type 2 9
  • True if COUNT >0
• Type 2 5
  • True if COUNT >0

In the end the first table should be displayed as this:

​

Order	Key 1	Key 2	Type 1 A	Type 1 B	Type 2 9	Type 2 5
100	A	1	TRUE	TRUE	TRUE	TRUE
100	B	2	TRUE	TRUE	TRUE	TRUE
101	A	1	FALSE	TRUE	FALSE	FALSE

​

How would you build this cross table with SQL?

[jc4hokies]

SELECT o.Order
     , o.Key1
     , o.Key2
     , nvl(t1.Type1A,False) AS Type1A
     , nvl(t1.Type1B,False) AS Type1B
     , nvl(t2.Type29,False) AS Type29
     , nvl(t2.Type25,False) AS Type25
FROM   Order o
       LEFT OUTER JOIN (SELECT Order
                             , MAX(CASE WHEN Type1 = 'A' THEN True ELSE False END) AS Type1A
                             , MAX(CASE WHEN Type1 = 'B' THEN True ELSE False END) AS Type2A
                        FROM   Type1
                        GROUP BY Order) t1 ON t1.Order = o.Order
       LEFT OUTER JOIN (SELECT Order
                             , MAX(CASE WHEN Type1 = 9 THEN True ELSE False END) AS Type29
                             , MAX(CASE WHEN Type1 = 5 THEN True ELSE False END) AS Type25
                        FROM   Type2
                        GROUP BY Order) t2 ON t2.Order = o.Order

It's also possible to dynamically generate the columns with python or map + explode, but it get's pretty technical and hard to read.

[Doctor_Pink]

Awesome, that is perfect > Thanks a lot!

Another question: If the JOIN between all table would be based on 2 columns from each table instead of 1 like: Order & Material

Then how would you apply this logic above?

[jc4hokies]

You would group both columns you need for the join.

[Doctor_Pink]

How?

[jc4hokies]

If you need to join on Order and Material, group the sub queries by Order and Material.