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

first of all, long post. thank taking time read thoroughly! :)

i have 3 tables follows (sorry long post, it's better clear...)

-- table structure 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','kids','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 table itemdata

insert `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 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 table orderitems

insert `orderitems` (`orderid`, `itemid`, `size`, `qty`) values (1, 123, 's', 1); 

-- table structure 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 table stockitems

insert `stockitems` (`itemid`, `size`, `qty`) values (124, 's', 5); 

two items tables: stockitems , orderitems
, 1 list table: itemdata

now, trying run single query @ both items tables (stockitems & orderitems , return information itemdata plus total quantity both in stock , ordered each itemid, colourway & size.

edit: forgot add 2 items tables little differently organised. stockitems table never ever have duplicate row, orderitems may have many of same row, of these 'duplicate' rows same except qty field may or may not different. need sum of qty field orderitems table, grouped size , itemid.

so ideally like:
itemid, brand, category, name, code, colourway, gender, discontinued, size, qty_stock, qty_orders

i have been experimenting different queries little on 2 hours , trying find article how accomplish i'd little success.

the thing managed required data, returned data existed in both orderitems , stockitems, need return data if item in stock or ordered.

here's query 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 itemdata id    join stockitems stk on (stk.itemid = id.itemid)   join orderitems ord on (ord.itemid = id.itemid) stk.size = ord.size 

any ideas?

answering asked for, how to

return information itemdata plus total quantity  both in stock , ordered each itemid, colourway & size 

first of don't store info colour in either orderinfo nor stockinfo table, there no way quantity depending on colour. let's ignore colour.

to qty both orderinfo , stockinfo based on itemid , size without being sure there row given id , size in of these tables need full outer join, not supported in mysql, can achieve same result using 2 mirror left joins , union. orderinfo table need group itemid , size sum qty within each group. once have both tables joined can inner join result itemdata table on itemid info each item. don't need touch sizerange don't need check in query if sizes match possible sizerange. final query should like:

select oisi.size,oisi.oqty,oisi.sqty,id.* itemdata id join   ( select oi.itemid,oi.size,oi.oqty,coalesce(si.qty,0) sqty          ( select oid.itemid, oid.size, sum( qty ) oqty                 orderitems oid                 group oid.itemid, oid.size ) oi          left join stockitems si           on si.itemid=oi.itemid , si.size=oi.size          union           select si.itemid,si.size,coalesce(oi.oqty,0) oqty,si.qty sqty           stockitems si          left join ( select oid.itemid, oid.size, sum( qty ) oqty                 orderitems oid                 group oid.itemid, oid.size ) oi          on si.itemid=oi.itemid , si.size=oi.size ) oisi on id.itemid=oisi.itemid 

its restrict result set combination of category, brand, gender , on adding @ end , sort whatever column wish using order by.

i suggest do:

alter table orderitems add index item_size( itemid, size ) ; 

as use these columns group by.