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 :
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 :
products_categories.productid = products.productid
products.productid IS NULL &&
products.avail > 0 &&
products.forsale = 'y'
Any help will be most appreciated.
John C. Nichel IV
Dot Com Holdings of Buffalo