I'm not sure I followed everything you said, but if products.productid is NULL then I think you will only find those categories that have no matching products in the products_categories table. Is that what you want?
Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 > -----Original Message----- > From: John Nichel [mailto:[EMAIL PROTECTED] > Sent: Friday, December 01, 2006 11:10 AM > To: mysql@lists.mysql.com > Subject: Selecting data from three tables > > Hi, > > I am having a bit of trouble with a SQL query that I am hoping > someone here can give me pointers on, or a nudge in the > direction of a > place which can give me a better understanding of JOINS. > > Running MySQL v4.0.27 Max on a RHEL4 box. I have three tables which > look like this... > > Table categories : > categoryid int(11) > parentid int(11) > category varchar(255) > avail char(1) > template_id int(11) > > Table products_categories > categoryid int(11) > productid int(11) > > Table products > productid int(11) > avail int(11) > forsale char(1) > > The short of it is, I'm trying to find all categories which contain > *only* products which have products.forsale = y AND > products.avail <= 0. > ie, I'm looking for categories in our system which have products > associated with them BUT none of the products are available for sale > (forsale flag set but no stock). > > I would also like to limit this to just the categories which have the > following values: parentid > 0 AND template_id <= 0 AND > categories.avail > = y, but being that I haven't been able to even nail down the simple > part of this, I'll go with what I can get to work for now. > > I've tried numerous combinations of JOINS, and while the queries are > executing fine, they're not returning any data (and I made > sure that at > least on category had products assigned to it, and that all > the products > assigned to it had a stock (avail) of 0). The simplest form > I came up > with (just trying to work with the last two tables, > products_categories > and products) was this : > > SELECT > categoryid, > products_categories.productid > FROM > products_categories > LEFT JOIN > products > ON > products_categories.productid = products.productid > WHERE > products.productid IS NULL && > products.avail > 0 && > products.forsale = 'y' > > Any help will be most appreciated. > > -- > John C. Nichel IV > Programmer/System Admin > Dot Com Holdings of Buffalo > 716.856.9675 > [EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
