Instead of providing ascii tables, consider create table
and insert
statements. This is what I assume:
create table hosts
( host_object_id int not null primary key
, hostname varchar(20) not null);
create table vars
( object_id int not null
, varname varchar(20) not null
, varvalue varchar(20) not null
, primary key (object_id, varname));
insert into hosts (...) values (1,'America')
, (2, 'Another1');
insert into vars (...) values (1,'type','Country')
, (1, 'location', 'N.A')
, (1,'capital', 'D.C');
This appears to be some kind of EAV construction, this may be tempting since the structure is flexible, but also means that your queries soon will be very complicated and performance will suffer.
You can use a case statement to grab the correct varname
and then choose one that is not null via - for example - max (any value is greater than null):
select hostname, max(type), max(location)
from (
select h.hostname
, case when v.varname = 'type' then v.varvalue end as type
, case when v.varname = 'location' then v.varvalue end as location
from hosts h
join vars v
on h.host_object_id = v.object_id
) as t
group by hostname;
You can shorten this a bit by applying max without the outer select:
select h.hostname
, max(case when v.varname = 'type' then v.varvalue end) as type
, max(case when v.varname = 'location' then v.varvalue end) as location
from hosts h
join vars v
on h.host_object_id = v.object_id
group by h.hostname;
If at all possible consider changing the vars table to something like:
create table vars
( object_id int not null primary key
, type varchar(20) not null
, location char(4) not null
, capital char(...) not null)
Not only will it be beneficial performance wise, it will also give you much better control of the domain for type, location, etc.
JOIN
is right to give you multiple rows. Please show us the actual query.