MySQL - Complex query including lookup columns from several tables

Question

Here we go again...

[EDIT] There is an update at the bottom with SQL to generate a test database. [EDIT]

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.

I will try to walk you through the setup so you understand what I'm trying to do. Please, be patient...

Background

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.

IntegerAttributes
ID  Key       Value
1   Location  3
1   Color     5
2   Location  1
3   Color     3

The value for the attribute 'Location' is an ID in another table called 'Locations'

Locations
ID Location etc...
1  Boston
2  Manilla
3  Stockholm
4  Beijing

The value for the attribute 'Color' is an ID in another table called 'Colors'

Locations
ID Color
1  Blue
2  Black
3  White
4  Red
5  Green

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.

The table holding each Item is very simple at this point:

ID  Checkin
1   2010-01-22 11:28:18
2   2010-01-21 16:27:54
3   2010-01-20 18:40:07

So far everything is clear, but now it gets complicated:

This is the SQL Query I use to fetch all attributes and concatenate them into a JSON string and fetching the Item data

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

As you can see I have remarked two lines, I will get back to those shortly.

Problem

The result of the query above would be something like this:

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"}

So far so good.

But now I would like to include the lookup of 'Location' (and eventually later on 'Color' or other attributes with a lookup-ID).

If I uncomment the two lines in the query above

# , l.Location
# JOIN locations l ON (Attribute.Key = 'Location' AND l.ID = Attribute.Value)

I only get the results where the only attribute is 'Location' and the Attributes field will now only includes the 'Date' and 'Location' attributes.

ID  Checkin              Attributes                                     Location
2   2010-01-21 16:27:54  {"Date":"2010-01-21 16:27:54","Location":"1"}  Boston

Desired result

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.

Example of what I want:

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

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.

[EDIT] SQL TO GENERATE A TEST DATABASE [EDIT]

-- 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 */;

I tried out the suggestion from Tumbler with this updated Query:

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

...but it does displays all the attributes, but only for Items that have Location or color as their ONLY attribute.

Quassnoi · Accepted Answer

SELECT  i.ID, i.checkin,
        CONCAT('{\"Date\":\"', i.checkin,'\",', GROUP_CONCAT('\"', attribute.key, '\":\"', CONVERT(attribute.value,CHAR), '\"'), '}') as attributes,
        l.location, c.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
JOIN    items i
ON      i.ID = attribute.itemid
LEFT JOIN
        attributeintegers atli
ON      atli.itemid = i.id
        AND atli.key = 'Location'
LEFT JOIN
        locations l
ON      l.id = atli.value
LEFT JOIN
        attributeintegers atci
ON      atci.itemid = i.id
        AND atci.key = 'Color'
LEFT JOIN
        colors c
ON      c.id = atci.value
GROUP BY
        i.id DESC

MySQL - Complex query including lookup columns from several tables

Background

Problem

Desired result

Answers (2)

Related Questions