The query you suggested with two tables is a great start.
If you make your query into an inline-view (which treats
a query like a table) you could now connect the categories
table to your suggested query:
SELECT
AA.categoryid,
AA.productid
FROM
(
SELECT
A.categoryid,
A.productid
FROM
products_categories A
LEFT JOIN
products B
ON
A.productid = B.productid
WHERE
B.productid IS NULL &&
B.avail > 0 &&
B.forsale = 'y'
) AA, categories BB
WHERE AA.productid = BB.productid
AND AA.categoryid = BB.categoryid
;
Give it a try !!!
----- Original Message -----
From: John Nichel <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Friday, December 1, 2006 11:09:52 AM GMT-0500 US/Eastern
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]
