Re: joins

[Armand Turpel]

Hi,

I think there where 2 possibilities.
1. Add to the customers_basket table the categories_id
SELECT
   cat.*
FROM
   customers_basket AS cb,
   categories AS cat
WHERE
   products_id=7
AND
   cb.categories_id=cat.categories_id
2. Make a relation table

CREATE TABLE relation_product_category (
 products_id int NOT NULL,  
 categories_id int NOT NULL
);

SELECT
   cat.*
FROM
   relation_product_category AS rel,
   categories AS cat
WHERE
   rel.products_id=7


atur

--------------------------------------------------

Andrew wrote:

Hi MySQL

I want to run a result through a routine but I need to get the result first :)

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)
);
Any ideas?

Thanks
andrew


 



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]