i have a function that let user to delete a selected brand. For example:
if the user choose brand_id='br45', then i have to
1. find all the prod_id in the product table using the brand_id.
2. delete the selected brand in the brand table using the brand_id.
3. delete the product in the brand table using the brand_id.
4. delete the selected brand's product in price_master using the prod_id found in the first step.
My problem is if the brand's has a few product's in the price_master, how can i delete all of them?
Or can any other ways to do it?
One more question is : is it that i can only delete one row of a table at a time?
CREATE TABLE CATEGORY
(
    CAT_ID INT NOT NULL,
    CAT_NAME VARCHAR(30) NOT NULL,
    PRIMARY KEY (CAT_ID),
) ;

CREATE TABLE BRAND
(
    BRAND_ID VARCHAR(5) NOT NULL,
    BRAND_NAME VARCHAR(30) NOT NULL,
    CAT_ID INT NOT NULL,
    PRIMARY KEY (BRAND_ID),
    FOREIGN KEY (CAT_ID) REFERENCES CATEGORY,
);

CREATE TABLE PRODUCT
(
    PROD_ID VARCHAR(5) NOT NULL,
    PROD_NAME VARCHAR(50) NOT NULL,
    PROD_DESC VARCHAR(20) NOT NULL,
    PROD_PRICE DECIMAL(8,2) NOT NULL,
    PROD_BRAND VARCHAR(5) NOT NULL,
    PRIMARY KEY(PROD_ID),
    FOREIGN KEY(PROD_BRAND) REFERENCES BRAND,
);

CREATE TABLE PRICE_MASTER
(
    PROD_ID VARCHAR(5) NOT NULL,
    START_DATE DATE NOT NULL,
    END_DATE DATE NOT NULL,
    DISKAUN_PRICE DECIMAL(4,2) NOT NULL,
    PRIMARY KEY (PROD_ID),
);

No need liao. i solve it already.