Difficult with join statements?

Hi guys.

I need to create three tables.

flatpack_apn

component_apn

flatpackcomponent_apn

or the table flatpackcomponent, I need to have three values:

FlatpackID, ComponentNo, Quantity

FlatpackID which is from the flatpack table

componentNo which is from the component table

Quantity which is an entirely new value.

My SQL for the first two tables are as follows:

CREATE TABLE flatpack_apn (

flatpackid int NOT NULL AUTO_INCREMENT,

name text (20) NOT NULL,

colour text NULL,

flatpacktype ENUM ('Office', 'Kitchen', 'Bedroom', 'General'),

unitprice decimal (5,2),

CONSTRAINT CHK_unitprice CHECK(unitprice>=5.00 AND unitprice<=500.00),

CONSTRAINT flatpack_apn_pk PRIMARY KEY (flatpackid)

);

CREATE TABLE component_apn (

componentno int NOT NULL AUTO_INCREMENT,

description varchar(30) NOT NULL,

CONSTRAINT component_apn_pk PRIMARY KEY (componentno)

);

I thought that in order to create the flatpackcomponent_apn table I would use:

CREATE TABLE flatpackcomponents_apn (

SELECT flatpack_apn.flatpackid, component_apn.componentno,

CONSTRAINT CHK_quantity CHECK (quantity>=1)

);

However, I get an error SQL 1109, "unknown table". However, whenever I enter a table name then . I get a list of the possible values to choose from!? What is going on

Look like you mix create table statements with insert select statements. Either that or you are looking for a CREATE OR REPLACE TABLE flatpackcomponents_apn AS (SELECT flatpack_apn.flatpackid, component_apn.componentno FROM component_apn, flatpack_apn ); statement. That will create the pivot table and populate it with a x-product of all rows in thoose tables.