title: MySQL - Complex query including lookup columns from several tables
body: <p>Here we go again...</p>
  
  <p><strong>[EDIT] There is an update at the bottom with SQL to generate a test database. [EDIT]</strong></p>
  
  <p>I have struggled a few days now with, for me a rather complex, SQL query. I would never had get this far without all the great help from SO that have learned me a lot of SQL.</p>
  
  <p>I will try to walk you through the setup so you understand what I'm trying to do. Please, be patient...</p>
  
  <h1>Background</h1>
  
  <p>I have one table with integer attributes (IntegerAttributes). The ID is an Item ID where the Key is the name of an attribute attached to the Item with ID 'ID' and the Value is the attribute's value and can only be an integer. One Item can have 0 to many attributes.</p>
  
  <pre><code>IntegerAttributes
  ID  Key       Value
  1   Location  3
  1   Color     5
  2   Location  1
  3   Color     3
  </code></pre>
  
  <p>The value for the attribute 'Location' is an ID in another table called 'Locations'</p>
  
  <pre><code>Locations
  ID Location etc...
  1  Boston
  2  Manilla
  3  Stockholm
  4  Beijing
  </code></pre>
  
  <p>The value for the attribute 'Color' is an ID in another table called 'Colors'</p>
  
  <pre><code>Locations
  ID Color
  1  Blue
  2  Black
  3  White
  4  Red
  5  Green
  </code></pre>
  
  <p>There are also a similar table for strings (StringAttributes) that works in the same way as IntegerAttributes, except the Value column is text. Both IntegerAttributes and StringAttributes are combined and converted into strings with a UNION ALL.</p>
  
  <p>The table holding each Item is very simple at this point:</p>
  
  <pre><code>ID  Checkin
  1   2010-01-22 11:28:18
  2   2010-01-21 16:27:54
  3   2010-01-20 18:40:07
  </code></pre>
  
  <p>So far everything is clear, but now it gets complicated:</p>
  
  <p>This is the SQL Query I use to fetch all attributes and concatenate them into a JSON string and fetching the Item data</p>
  
  <pre><code>SELECT
    i.ID,
    i.Checkin,
    CONCAT('{\"Date\":\"',i.Checkin,'\",', GROUP_CONCAT('\"',Attribute.key, '\":\"', CONVERT(Attribute.value,CHAR), '\"'), '}') as Attributes
    # , l.Location
  FROM  (
    SELECT ItemID, ats.Key, ats.Value
    FROM attributeStrings as ats 
    UNION ALL
    SELECT ItemID, ati.Key, ati.Value
    FROM attributeIntegers as ati
  ) Attribute
  JOIN Items i ON i.ID = Attribute.ItemID
    # JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)
  GROUP BY ItemID
  ORDER BY i.ID DESC
  </code></pre>
  
  <p>As you can see I have remarked two lines, I will get back to those shortly.</p>
  
  <h1>Problem</h1>
  
  <p>The result of the query above would be something like this:</p>
  
  <pre><code>ID  Checkin              Attributes
  1   2010-01-22 11:28:18  {"Date":"2010-01-22 11:28:18","Location":"3","Color":"5"}
  2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}
  3   2010-01-20 18:40:07  {"Date":"2010-01-20 18:40:07","Color":"3"}
  </code></pre>
  
  <p>So far so good.</p>
  
  <p>But now I would like to include the lookup of 'Location' (and eventually later on 'Color' or other attributes with a lookup-ID).</p>
  
  <p>If I uncomment the two lines in the query above</p>
  
  <pre><code># , l.Location
  # JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)
  </code></pre>
  
  <p>I only get the results where the only attribute is 'Location' and the Attributes field will now only includes the 'Date' and 'Location' attributes.</p>
  
  <pre><code>ID  Checkin              Attributes                                     Location
  2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}  Boston
  </code></pre>
  
  <h1>Desired result</h1>
  
  <p>The result I want to have is as before but with an extra column with the 'Location' (and later on more lookup columns) attribute looked up in Locations.</p>
  
  <p>Example of what I want:</p>
  
  <pre><code>ID  Checkin              Attributes                                                 Location   Color
  1   2010-01-22 11:28:18  {"Date":"2010-01-22 11:28:18","Location":"3","Color":"5"}  Stockholm  Green
  2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}              Boston     null
  3   2010-01-20 18:40:07  {"Date":"2010-01-20 18:40:07","Color":"3"}                 null       White
  </code></pre>
  
  <p>Thank you for reading all the way down here :D
  I have tried to elaborate with the IF EXISTS but I can't figure out how to do this.</p>
  
  <p><strong>[EDIT] SQL TO GENERATE A TEST DATABASE [EDIT]</strong></p>
  
  <pre><code>-- MySQL Administrator dump 1.4
  --
  -- ------------------------------------------------------
  -- Server version   5.1.47-community
  
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8 */;
  
  /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
  /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
  /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
  
  
  --
  -- Create schema swebussandbox
  --
  
  CREATE DATABASE IF NOT EXISTS SODatabase;
  USE SODatabase;
  
  --
  -- Definition of table `attributeintegers`
  --
  
  DROP TABLE IF EXISTS `attributeintegers`;
  CREATE TABLE `attributeintegers` (
    `ItemID` int(10) unsigned NOT NULL,
    `Key` varchar(45) NOT NULL,
    `Value` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`ItemID`,`Key`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  
  --
  -- Dumping data for table `attributeintegers`
  --
  
  /*!40000 ALTER TABLE `attributeintegers` DISABLE KEYS */;
  INSERT INTO `attributeintegers` (`ItemID`,`Key`,`Value`) VALUES
   (4,'Color',17),
   (4,'Location',3),
   (5,'Location',2),
   (6,'Location',6),
   (7,'Color',15),
   (8,'Location',8),
   (9,'Location',10),
   (10,'Color',15),
   (10,'Location',2),
   (11,'Color',15),
   (11,'Location',4),
   (12,'Color',15),
   (12,'Location',3),
   (13,'Color',15),
   (13,'Location',8),
   (14,'Location',3),
   (15,'Location',6),
   (16,'Color',18),
   (18,'Color',15),
   (18,'Location',4);
   /*!40000 ALTER TABLE `attributeintegers` ENABLE KEYS */;
  
  
  --
  -- Definition of table `attributestrings`
  --
  
  DROP TABLE IF EXISTS `attributestrings`;
  CREATE TABLE `attributestrings` (
    `ItemID` int(10) unsigned NOT NULL DEFAULT '0',
    `Key` varchar(45) NOT NULL DEFAULT '_NA_',
    `Value` text,
    PRIMARY KEY (`ItemID`,`Key`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  
  --
  -- Dumping data for table `attributestrings`
  --
  
  /*!40000 ALTER TABLE `attributestrings` DISABLE KEYS */;
  INSERT INTO `attributestrings` (`ItemID`,`Key`,`Value`) VALUES
   (5,'Type','BagForm'),
   (6,'Type','BagForm'),
   (9,'Type','BagForm'),
   (10,'Type','BagForm'),
   (11,'Type','BagForm'),
   (12,'Brand','Bogcase'),
   (12,'Type','BagForm'),
   (14,'Type','BagForm'),
   (15,'Brand','Carryline World Wide'),
   (15,'Type','BagForm'),
   (16,'Brand','Fjällräven'),
   (16,'Type','BagForm'),
   (17,'Brand','Packard Bell'),
   (17,'Tech','ComputerForm'),
   (17,'Type','TechGUI');
  /*!40000 ALTER TABLE `attributestrings` ENABLE KEYS */;
  
  
  
  --
  -- Definition of table `colors`
  --
  
  DROP TABLE IF EXISTS `colors`;
  CREATE TABLE `colors` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `Color` varchar(45) NOT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
  
  --
  -- Dumping data for table `colors`
  --
  
  /*!40000 ALTER TABLE `colors` DISABLE KEYS */;
  INSERT INTO `colors` (`ID`,`Color`) VALUES
   (1,'Multicolored'),
   (2,'Black'),
   (3,'White'),
   (4,'Red'),
   (5,'Green'),
   (6,'Blue'),
   (7,'Yellow'),
   (8,'Black'),
   (9,'Gold'),
   (10,'Bown'),
   (11,'Purpul'),
   (12,'Pink'),
   (13,'Orange'),
   (14,'Gray'),
   (15,'Transparent');
  /*!40000 ALTER TABLE `colors` ENABLE KEYS */;
  
  
  
  --
  -- Definition of table `items`
  --
  
  DROP TABLE IF EXISTS `items`;
  CREATE TABLE `items` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `CheckIn` datetime DEFAULT NULL,
    `Line` int(10) unsigned DEFAULT NULL,
    `TypeID` int(10) unsigned DEFAULT NULL,
    `SizeID` int(10) unsigned DEFAULT NULL,
    `ColorID` int(10) unsigned DEFAULT NULL,
    `MaterialID` int(10) unsigned DEFAULT NULL,
    `CheckOut` datetime DEFAULT NULL,
    `LocationID` int(10) unsigned DEFAULT NULL,
    `Notes` text,
    `Tur` int(10) unsigned DEFAULT NULL,
    `Bus` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`ID`),
    FULLTEXT KEY `NoteIndex` (`Notes`)
  ) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 PACK_KEYS=1;
  
  --
  -- Dumping data for table `items`
  --
  
  /*!40000 ALTER TABLE `items` DISABLE KEYS */;
  INSERT INTO `items` (`ID`,`CheckIn`) VALUES
   (4,'2010-12-03 02:04:38'),
   (5,'2010-12-27 02:11:25'),
   (6,'2010-12-27 02:14:28'),
   (7,'2010-12-25 02:17:09'),
   (8,'2010-12-24 02:33:49'),
   (9,'2011-01-06 07:48:16'),
   (10,'2011-01-06 07:47:09'),
   (11,'2010-12-31 10:53:26');
  /*!40000 ALTER TABLE `items` ENABLE KEYS */;
  
  
  --
  -- Definition of table `locations`
  --
  
  DROP TABLE IF EXISTS `locations`;
  CREATE TABLE `locations` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `Location` varchar(45) NOT NULL,
    `Group` int(10) unsigned NOT NULL DEFAULT '1',
    `Address` text,
    `Phone` varchar(20) DEFAULT NULL,
    `Contact` varchar(45) DEFAULT NULL,
    `Hours` varchar(45) DEFAULT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
  
  --
  -- Dumping data for table `locations`
  --
  
  /*!40000 ALTER TABLE `locations` DISABLE KEYS */;
  INSERT INTO `locations` (`ID`,`Location`) VALUES
   (1,'Boston'),
   (2,'Stockholm'),
   (3,'Manilla'),
   (4,'Berlin'),
   (5,'Oslo'),
   (6,'Paris'),
   (7,'London'),
   (8,'Amsterdam'),
   (9,'Helsinki'),
   (10,'Kopenhagen'),
   (11,'Barselona'),
   (12,'Luxenbourg'),
   (13,'Milano');
  /*!40000 ALTER TABLE `locations` ENABLE KEYS */;
  
  /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
  /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
  /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  </code></pre>
  
  <p>I tried out the suggestion from <strong>Tumbler</strong> with this updated Query:</p>
  
  <pre><code>SELECT
    i.ID,
    i.Checkin,
    CONCAT('{\"Date\":\"',i.Checkin,'\",', GROUP_CONCAT('\"',Attribute.key, '\":\"', CONVERT(Attribute.value,CHAR), '\"'), '}') as Attributes,
    COALESCE(l.Location,null) as Location,
    COALESCE(c.Color,null) as Color
  FROM  (
    SELECT ItemID, ats.Key, ats.Value
    FROM attributeStrings as ats
    UNION ALL
    SELECT ItemID, ati.Key, ati.Value
    FROM attributeIntegers as ati
  ) Attribute
  LEFT JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)
  LEFT JOIN Colors c ON (Attribute.Key = 'Color' AND c.ID = Attribute.Value)
  JOIN Items i ON i.ID = Attribute.ItemID
  GROUP BY ItemID
  ORDER BY i.ID DESC
  </code></pre>
  
  <p>...but it does displays all the attributes, but only for Items that have Location or color as their ONLY attribute.</p>