Hi all... I've not been here for AGES... but I hope many of you trusted clever people are still out there... I need help! ;-p
I'm building a shop for a client. I've got 3 tables I'm trying to talk to. products stock categories - Each product can be flagged with any numebr of cateogories using a single field, with csv (12, 52, 101) (it's a T-shirt shop, so the categories are for eg: film, quotes, funny, sports etc) - in the stock table, each product is asigned several stock fields, of each size/gender type. - and the categories are all stored in a seperate table, for admin purposes. What I want to do is to only get a category name from the database, IF one or any of the products are in stock. Here's what I've got so far.. Any MySQL gurus out there? :-( ============= SELECT categories.displayName, stock.count FROM categories, products, stock WHERE products.styleCategory LIKE '%categories.id%' AND (stock.count != "0" AND stock.size = '4') OR (stock.count != "0" AND stock.size = '5') OR (stock.count != "0" AND stock.size = '6') OR (stock.count != "0" AND stock.size = '7') OR ( stock.count != "0" AND stock.size = '8') OR (stock.count != "0" AND stock.size = '9') AND categories.type = '5' ================= my table structure if it helps: CREATE TABLE `categories` ( `id` int(11) NOT NULL auto_increment, `shortName` varchar(255) NOT NULL default '', `displayName` varchar(255) NOT NULL default '', `type` int(2) NOT NULL default '0', `active` int(1) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=116 ; -- -------------------------------------------------------- -- -- Table structure for table `products` -- CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `productCode` varchar(100) NOT NULL default '', `styleCategory` varchar(255) NOT NULL default '', `title` varchar(255) NOT NULL default '', `desc` mediumtext NOT NULL, `price` decimal(4,2) NOT NULL default '0.00', `salePrice` decimal(4,2) NOT NULL default '0.00', `saleStatus` int(1) NOT NULL default '0', `active` int(1) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -------------------------------------------------------- -- -- Table structure for table `stock` -- CREATE TABLE `stock` ( `id` int(7) NOT NULL auto_increment, `productId` int(4) NOT NULL default '0', `size` int(2) NOT NULL default '0', `count` int(5) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1192 ; ____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or use the web interface http://e-newsletters.internet.com/discussionlists.html/ Send Your Posts To: [email protected] To change subscription settings, add a password or view the web interface: http://intm-dl.sparklist.com/read/?forum=wdvltalk ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] To unsubscribe via postal mail, please contact us at: Jupitermedia Corp. Attn: Discussion List Management 475 Park Avenue South New York, NY 10016 Please include the email address which you have been contacted with.
