0

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 |
2
  • JOIN is right to give you multiple rows. Please show us the actual query.
    – Rick James
    Jul 12, 2017 at 19:45
  • Did you find a good solution to your question?
    – joanolo
    Jul 18, 2017 at 18:25

2 Answers 2

3

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.