Toad World® Forums

Trying to combine two statements but the syntax is wrong?


#1

Here’s my two statements:

select * from climate_trends.CT05_baseline_values
inner join climate_trends.CT03_grid_boxes
on climate_trends.CT05_baseline_values.location_id = climate_trends.CT03_grid_boxes.grid_box

select * from CLIMATE_TRENDS.CT05_baseline_values
where averaging_period_id in(‘Spr’,‘Sum’,‘Aut’,‘Win’)
and climate_variable_id in(‘MeanTemp’)
and location_type_id = ‘Box’
and baseline_period = ‘1981-2010’

The first statement joins the 2 tables together using 2 columns that have different names but the same data, I need to do this because the CT03 table contains my spatial reference values with the data that’s in CT05. I hope this makes sense, any help would be appreciated!


#2

if climate_trends.CT05_baseline_values.location_id = climate_trends.CT03_grid_boxes.grid_box can uniquely identify a record from CT03_grid_boxes, a simple join can solve the problem and run faster.

select * from CLIMATE_TRENDS.CT05_baseline_values,climate_trends.CT03_grid_boxes

where averaging_period_id in(‘Spr’,‘Sum’,‘Aut’,‘Win’)

and climate_variable_id in(‘MeanTemp’)

and location_type_id = ‘Box’

and baseline_period = ‘1981-2010’

and climate_trends.CT05_baseline_values.location_id = climate_trends.CT03_grid_boxes.grid_box

if climate_trends.CT05_baseline_values.location_id = climate_trends.CT03_grid_boxes.grid_box CANNOT uniquely identify a record from CT03_grid_boxes, this may help:

select * from CLIMATE_TRENDS.CT05_baseline_values,(select distinct spatial_reference_values , grid_box from climate_trends.CT03_grid_boxes) CT03

where averaging_period_id in(‘Spr’,‘Sum’,‘Aut’,‘Win’)

and climate_variable_id in(‘MeanTemp’)

and location_type_id = ‘Box’

and baseline_period = ‘1981-2010’

and climate_trends.CT05_baseline_values.location_id = CT03.CT03_grid_boxes.grid_box

Richard


#3

Let’s use these two simple tables as an example - this script creates 2 tables with different column names but same data types, and then tries to combine their select commands:

drop table table_1;
drop table table_2;

create table table_1 (col_x1 int, col_x2 int);
insert into table_1 values (1, 2);
insert into table_1 values (3, 4);
commit;

create table table_2 (col_y1 int, col_y2 int);
insert into table_2 values (5, 6);
insert into table_2 values (7, 8);
commit;

select * from table_1;
select * from table_2;

select * from table_1 where 1=1
UNION
select * from table_2 where 1=1;

select col_x1, col_x2 from table_1 where 1=1
UNION
select col_y1, col_y2 from table_2 where 1=1;

select col_x1 as COL1, col_x2 as COL2 from table_1 where 1=1
UNION
select col_y1, col_y2 from table_2 where 1=1;

Often the UNION command can solve this as shown by the three SELECT’s shown above. The first SELECT with asterisk works in this example because the tables have the same number of columns and they occur in the same order as far as data types. Most times this simple approach will not work - i.e. get errors. The second SELECT overcomes this first potential issue by specifically naming the columns rather than using asterisks. That works in most cases, but note that the output column header is the name from the first table - so maybe we want something different like COL1 and COL2. Thus in the third SELECT we simply use an alias in the first part of the UNION to name the output columns as we like. And now we have far friendly output :slight_smile:


#4

Bert, you are working on vertical combine, I am working on horizontal combine, it is fun !


#5

Yes - you are 100% correct. It just seemed like user was asking basic SQL how to type question adn could benefit from seeing both types of problems solved. We make a great tag team :slight_smile:


#6

Yes :slight_smile:


#7

Thanks for this Richard. I used the first statement you sent and managed to create a map based on the data values so I am more than happy!