vdEjZJEZ

1create external table if not exists survey(
2id string,
3category_name string, 
4subcategory_name string)
5STORED AS parquet;
6
7insert into survey(id, category_name, subcategory_name) 
8values ('1', 'Engine', 'Engine problem other than listed');
9insert into survey(id, category_name, subcategory_name) 
10values ('1', 'Exterior Body', 'Color match of painted parts');
11insert into survey(id, category_name, subcategory_name) 
12values ('1', 'Exterior Body', 'Tail lights');
13insert into survey(id, category_name, subcategory_name) 
14values ('1', 'Heating/Ventilation and Cooling', 'Front windshield fogs up');
15insert into survey(id, category_name, subcategory_name) 
16values ('1', 'Transmission', 'Rough shifting');
17
18create external table if not exists survey_comments(
19id string,
20category_name_txt string, 
21subcategory_name_txt string,
22comments string)
23STORED AS parquet;
24
25insert into survey_comments(id, category_name_txt, subcategory_name_txt) 
26values ('1', 'Exterior Body', 'Tail lights', 'Moisture in lower portion of rear tail lights along with leaves etc.');
27insert into survey_comments(id, category_name_txt, subcategory_name_txt) 
28values ('1', 'Heating/Ventilation and Cooling', 'Front windshield fogs up', 'Small amount of fog low on front windshield during/after rain.');
29insert into survey_comments(id, category_name_txt, subcategory_name_txt) 
30values ('1', 'Miscellaneous', 'General problem other than listed', 'When filling vehicle with gas; the pumps fill the gas line too quickly, had to hold the pump handle only 1/2 way on.');
31insert into survey_comments(id, category_name_txt, subcategory_name_txt) 
32values ('1', 'Miscellaneous', 'General problem other than listed', 'Touch-up paint too red, not same red as on the car.'); 
33     
34select b.id, b.category_name, b.subcategory_name, a.category_name_txt, a.sub_category_name_txt, a.comments
35from   survey b full outer join survey_comments a 
36on (
37b.id = a.id and              
38b.category_name     = a.category_name_txt and                      b.subcategory_name  = a.sub_category_name_txt
39)