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

eavjoin;MySQLpivot

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 |

Best Answer

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

Related Question