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
>
>
>
>  
>