Jump to content

Archived

This topic is now archived and is closed to further replies.

onlyican

JOIN and INSERT

Recommended Posts

Hey guys

I am learning this Join stuff, Wicked
I got the hang of INNER JOIN for SELECT
but

I have 3 tables, and I want to insert data in all 3, without doing 3 queries

I tried this
INSERT INTO products
LEFT JOIN prices on products.id_num = prices.prod_id
LEFT JOIN stock on products.id_num = stock.prid_id
(ref_num, prod_name, suppliers, cat, sub_cat, buy_price, iva, re, total_price, sell_price, stock_level, in_stock)
VAlUES('1006', 'Cat Food', '3', 'Cat', 'Food' '8.15', '0', '0', '8.15', '10.20', '5', '5');

but I got an error on Inner the Inner Join

NOTE: this is dummy information, like a shop will buy something for 8.15 and sell it for 10.20)

All this information is coming from a form

Here is the table information for the 3 tables

CREATE TABLE IF NOT EXISTS products (
  id_num int(50) unsigned NOT NULL auto_increment,
  ref_num varchar(10) DEFAULT '0' ,
  prod_name varchar(150) DEFAULT '0' ,
  supplier varchar(50) DEFAULT '0' ,
  cat varchar(150) ,
  sub_cat varchar(150) ,
  PRIMARY KEY (id_num)
);

CREATE TABLE IF NOT EXISTS prices (
  id_num int(50) NOT NULL auto_increment,
  prod_id int(50) DEFAULT '0' ,
  buy_price double(5,2) DEFAULT '0.00' ,
  iva int(3) unsigned DEFAULT '0' ,
  re double(2,1) DEFAULT '0.0' ,
  total_price double(5,2) DEFAULT '0.00' ,
  sell_price double(5,2) DEFAULT '0.00' ,
  PRIMARY KEY (id_num)
);

CREATE TABLE IF NOT EXISTS stock (
  id_num int(50) unsigned NOT NULL auto_increment,
  prod_id int(50) unsigned DEFAULT '0' ,
  stock_level tinyint(5) DEFAULT '0' ,
  in_stock tinyint(5) DEFAULT '0' ,
  PRIMARY KEY (id_num)
);

The Connection between the 3 is
prod_id in tables Stock and prices is the same as id_num in products

Anyone who can help, Many Thanks

Share this post


Link to post
Share on other sites
You have no choice but to issue one insert statment per table.

Share this post


Link to post
Share on other sites
I thought Joins work on INSERT and UPDATE
or is it only on SELECT with MySQL

Share this post


Link to post
Share on other sites
Only SELECT... though you can "use" it with INSERT INTO... SELECT WHERE.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.