Join tables with same keys, second table has multiple values for key

Question

I have two tables with shared keys and I'm trying to join them and output the results to one table. The problem I'm having is the second table has multiple values for the shared key.

hosts

host_object_id | hostname  | 
1              | America1  |
2              | Another1  |

vars

object_id      | varname   |  varvalue |
1              | type      |  Country  |
1              | location  |  N.A.     |
1              | capital   |  D.C.     |
2              | type      |  Another  |
2              | location  |  Location |
2              | capital   |  Example  |

What I'm trying to get is

Hostname       | Type      |  Location |
America        | Country   |  N.A.     |
Another1       | Another   |  Location |

Answer 1

You have two possibilities:

1. Two joins. Probably a good alternative if you do a limited search, and don't want all available attributes (i.e.: you ignore capital)

   SELECT
       hostname, 
       type_var.varvalue AS `type`, 
       location_var.varvalue AS location
   FROM
       hosts
       JOIN vars AS type_var  
           ON (type_var.object_id = hosts.host_object_id) AND  type_var.varname = 'type'
       JOIN vars AS location_var 
           ON (location_var.object_id = hosts.host_object_id) AND location_var.varname = 'location' ;
   

2. max(case) and GROUP BY. This is a typical approach to unpivot in standard SQL. This is normally faster went you either want all attributes (type, location, capital), or you don't limit your queries with a WHERE that restricts the result significantly. (In your case, without WHERE, I'd choose this approach:

   SELECT
       hostname, 
       max(case when varname = 'type'     then varvalue end) AS `type`, 
       max(case when varname = 'location' then varvalue end) AS location
   FROM
       hosts
       JOIN vars 
           ON (vars.object_id = hosts.host_object_id) 
   GROUP BY
       object_id ;
   

In both cases, you'll get:

hostname | type    | location
:------- | :------ | :-------
America1 | Country | N.A.    
Another1 | Another | Location

---

NOTE 1: This assumes that vars has PRIMARY KEY (object_id, varname). Otherwise, you could have more than one type or location per object_id, and that would give you more than one row per object_id in the first case, and just one in the second, choosing the max among them.

NOTE 2: These queries are standard SQL and should work not only on MySQL, but on most SQL RDBMS.

---

You can check everything at dbfiddle here

Answer 2

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.