# [EMAIL PROTECTED] / 2003-06-29 08:14:28 +0100:
> The table customer_basket contains the products_id which is no good
> for my routine. What I need to check is the categories_id but there
> is no reference in the customers_basket.
>
> Presumably I need to do a join on the table that holds the cat_id?
>
> So I already have the the product_id as this is in the customer_basket
> so to get the cat_id I need to select only the cat_id where
> product_id. Am I thinking straight on this problem, if so I cant
> seems to get a result
>
>
> here are the two tables:
>
> CREATE TABLE customers_basket (
> customers_basket_id int NOT NULL auto_increment,
> customers_id int NOT NULL,
> products_id tinytext NOT NULL,
> customers_basket_quantity int(2) NOT NULL,
> final_price decimal(15,4) NOT NULL,
> customers_basket_date_added char(8),
> PRIMARY KEY (customers_basket_id)
> );
>
> CREATE TABLE categories (
> categories_id int NOT NULL auto_increment,
> categories_image varchar(64),
> parent_id int DEFAULT '0' NOT NULL,
> sort_order int(3),
> date_added datetime,
> last_modified datetime,
> PRIMARY KEY (categories_id),
> KEY idx_categories_parent_id (parent_id)
> );
how is a product related to a category?
CREATE TABLE products (
product_id ...
category_id ...
product_desc ...
)
in this case you'll need something like this:
SELECT p.category_id
FROM products p, customers_basket cb
WHERE cb.customers_id = 123
AND cb.products_id = p.product_id
if a product can be in more than one category, you probably have
another table instead of the products.category_id column:
CRATE TABLE products_in_categories (
product_id ...
category_id ...
)
then you'd use this:
SELECT pic.category_id
FROM products_in_categories pic,
customers_basket cb
WHERE cb.customers_id = 123
AND cb.products_id = pic.product_id
--
If you cc me or remove the list(s) completely I'll most likely ignore
your message. see http://www.eyrie.org./~eagle/faqs/questions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
