Using MySQL to fetch data from two 'items' tables (both with different values), using a third 'itemlist' table

First of all, this is a long post. Thank you for taking the time to read it thoroughly! :)

I have three tables as follows (sorry for long post, but it's better to be clear...)

-- Table structure for table itemdata

CREATE TABLE IF NOT EXISTS `itemdata` (
  `itemID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `brand`  enum('dnc','fbiz','jbs','wspirit','aspirit','grace','legend','stencil','gfl','bocini','beseen','ljapp','lwreid') NOT NULL,
  `category` enum('shirts','headwear','winter','sports','bags','misc') NOT NULL,
  `name` varchar(255) NOT NULL,
  `code` varchar(20) NOT NULL,
  `colourway` varchar(255) NOT NULL DEFAULT 'Black',
  `gender` enum('mens','ladies','k开发者_StackOverflowids','unisex') NOT NULL,
  `sizerange` varchar(255) NOT NULL DEFAULT 'S|M|L|2XL|3XL|4XL|5XL',
  `discontinued` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`itemID`),
  KEY `name` (`name`,`code`,`gender`),
  KEY `brand` (`brand`),
  KEY `category` (`category`),
  KEY `discontinued` (`discontinued`),
  KEY `colourway` (`colourway`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=313 ;

-- Example data for table itemdata

INSERT INTO `itemdata` (`itemID`, `brand`, `category`, `name`, `code`, `colourway`, `gender`, `sizerange`, `discontinued`) VALUES (1, 'jbs', 'shirts', 'Fitted Tee', '1LHT1', 'Blue', 'ladies', '8|10|12|14|16|18|20', 0);

-- Table structure for table orderitems

CREATE TABLE IF NOT EXISTS `orderitems` (
  `orderID` int(10) unsigned NOT NULL,
  `itemID` int(11) NOT NULL,
  `size` enum('2','4','6','8','10','12','14','16','18','20','22','24','26','XS','S','M','L','XL','2XL','3XL','4XL','5XL','6XL','7XL','8XL','9XL','10XL','S/M','L/XL','N/A') NOT NULL,
  `qty` int(10) unsigned NOT NULL,
  KEY `orderid` (`orderID`,`size`),
  KEY `itemID` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Example data for table orderitems

INSERT INTO `orderitems` (`orderID`, `itemID`, `size`, `qty`) VALUES (1, 123, 'S', 1);

-- Table structure for table stockitems

CREATE TABLE IF NOT EXISTS `stockitems` (
  `itemID` int(11) NOT NULL,
  `size` enum('2','4','6','8','10','12','14','16','18','20','22','24','26','XS','S','M','L','XL','2XL','3XL','4XL','5XL','6XL','7XL','8XL','9XL','10XL','S/M','L/XL','N/A') NOT NULL,
  `qty` int(11) NOT NULL,
  KEY `size` (`size`),
  KEY `itemID` (`itemID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Example data for table stockitems

INSERT INTO `stockitems` (`itemID`, `size`, `qty`) VALUES (124, 'S', 5);

Two items tables: stockitems and orderitems

And one list table: itemdata

Now, what I am trying to do is run a single query that will look at both items tables (stockitems & orderitems and return the information from itemdata plus the total quantity both in stock and ordered for each itemID, colourway & size.

EDIT: I forgot to add that the two items tables are a little differently organised. The stockitems table will never ever have a duplicate row, where as the orderitems may have many of the same row, all of these 'duplicate' rows will be exactly the same except for the qty field which may or may not be different. I need to get a SUM of the qty field for the orderitems table, grouped by the size and itemID.

So ideally I would like:

itemID, brand, category, name, code, colourway, gender, discontinued, size, qty_stock, qty_orders

I have been experimenting with different queries for a little over 2 hours and trying to find an article about how to accomplish what I'd like to do with little success.

The only thing I managed was to get the required data, but it only returned data that existed in both orderitems AND stockitems, but I need it to return data even if the item is only in stock or only ordered.

Here's the query for that one:

SELECT id.itemID, id.category, id.brand, id.code, id.gender, id.name, id.colourway, 
  id.discontinued, stk.size, stk.qty, ord.size, ord.qty
FROM itemdata id 
  JOIN stockitems stk ON (stk.itemID = id.itemID)
  JOIN orderitems ord ON (ord.itemID = id.itemID)
WHERE stk.size = ord.size

Any ideas?

Answering what you really asked for, that is how to

return the information from itemdata plus the total quantity 
both in stock and ordered for each itemID, colourway & size

First of all you don't store any info about colour in either orderinfo nor stockinfo table, so there is no way to get quantity depending on the colour. So let's ignore the colour.

To get qty from both orderinfo and stockinfo based on itemID and size without being sure there is a row for given id and size in any of these tables you need FULL OUTER JOIN, which is not supported in mysql, but you can achieve the same result using two mirror LEFT JOINs and UNION. For the orderinfo table you need to GROUP BY itemID and size to sum qty within each group. Once you have both tables joined you can inner JOIN the result with itemdata table on itemID to get info for each item. You don't really need to touch sizerange as we don't need to check in this query if the sizes match possible sizerange. So the final query should look like:

SELECT oisi.size,oisi.oqty,oisi.sqty,id.* FROM
itemdata id
JOIN   ( SELECT oi.itemID,oi.size,oi.oqty,COALESCE(si.qty,0) as sqty
         FROM ( SELECT oid.itemID, oid.size, sum( qty ) as oqty
                FROM orderitems oid
                GROUP BY oid.itemID, oid.size ) oi
         LEFT JOIN stockitems si 
         ON si.itemID=oi.itemID AND si.size=oi.size
         UNION 
         SELECT si.itemID,si.size,COALESCE(oi.oqty,0) as oqty,si.qty as sqty 
         FROM stockitems si
         LEFT JOIN ( SELECT oid.itemID, oid.size, sum( qty ) as oqty
                FROM orderitems oid
                GROUP BY oid.itemID, oid.size ) oi
         ON si.itemID=oi.itemID AND si.size=oi.size ) oisi
ON id.itemID=oisi.itemID

Its up to you to restrict result set to any combination of category, brand, gender and so on adding WHERE at the end and sort it by whatever column you wish using ORDER BY.

I also suggest you do:

ALTER TABLE orderitems ADD INDEX item_size( itemID, size ) ;

as we use these columns for GROUP BY.

There are two things that cause rows to disappear if they are not present in both tables.

Both stockitems and orderitems are joined using an INNER JOIN (which JOIN aliases to). You also have a condition on stk.size = ord.size, which would never evaluate to TRUE for an item that doesn't exist in one of the tables.

Can we assume that orderID in orderitems and (itemID, size) in stockitems are PRIMARY KEYs?

If so you could get all entries with:

SELECT id.itemID, id.category, id.brand, id.code, id.gender, id.name, id.colourway, 
  id.discontinued, stk.size, stk.qty, ord.size, ord.qty
FROM itemdata id 
  LEFT OUTER JOIN stockitems stk ON (stk.itemID = id.itemID)
  LEFT OUTER JOIN orderitems ord ON (ord.itemID = id.itemID)

This will give us quite a nice list. The condition of size is missing, though. To fix this we have to define what should happen if a row exist on either side, but not the other of stockitems/orderitems.

We can't use an equal comparison, because if it is missing, the column will be filled with a NULL, and by definition, NULL is not equal to anything.

This is where this design is a little bit misdesigned for this type of queries, it would probably be easier if you added the the column sizerange as an additional table:

CREATE TABLE sizeranges (
  size CHAR(10) NOT NULL, -- or ENUM, to keep more consistent
  itemID INT NOT NULL,
  PRIMARY KEY (size, itemID)
);

Then we're not dependant on an item to exist in orderitems or stockitems for us to easily parse out the possible sizes for an item. This would then give us:

SELECT id.itemID, id.category, id.brand, id.code, id.gender, id.name, id.colourway, 
  id.discontinued, sr.size, IFNULL(stk.qty, 0), IFNULL(ord.qty, 0)
FROM itemdata id 
  INNER JOIN sizeranges sr ON (sr.itemID = id.itemID)
  LEFT OUTER JOIN stockitems stk ON (stk.itemID = id.itemID) AND (stk.size = sr.size)
  LEFT OUTER JOIN orderitems ord ON (ord.itemID = id.itemID) AND (ord.size = sr.size)

Hope this helps,