List:General Discussion« Previous MessageNext Message »
From:John Nichel Date:December 1 2006 4:09pm
Subject:Selecting data from three tables
View as plain text  
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
jnichel@stripped
Thread
Selecting data from three tablesJohn Nichel1 Dec
  • Re: Selecting data from three tablesRolando Edwards1 Dec
  • RE: Selecting data from three tablesJerry Schwartz1 Dec