Jump to content


Photo

JOIN and INSERT


  • Please log in to reply
3 replies to this topic

#1 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 18 September 2006 - 01:28 PM

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

Tell me the problem, I will try tell you the solution

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 September 2006 - 05:29 PM

You have no choice but to issue one insert statment per table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 onlyican

onlyican
  • Members
  • PipPipPip
  • Advanced Member
  • 921 posts
  • LocationHants - UK

Posted 18 September 2006 - 06:01 PM

I thought Joins work on INSERT and UPDATE
or is it only on SELECT with MySQL
Tell me the problem, I will try tell you the solution

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 20 September 2006 - 09:45 AM

Only SELECT... though you can "use" it with INSERT INTO... SELECT WHERE.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users