Mulitple checks accross tables...?

[beertastic]

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..
I just can;t get my head round it... I'm off for coffee... but I know it's gotta be simple!

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:

MySQL client version: 4.1.7

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 ;

[chazzy]

well for one, you shouldn't be using a comma delimited field value. impossible to search against.

once you do that, the solution is obviously

SELECT DISTINCT c.NAME from categories c, productsToCategories p where p.categoryId = c.categoryId;