Jump to content


Photo

Help with Query


  • Please log in to reply
1 reply to this topic

#1 onlyican

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

Posted 16 September 2006 - 03:44 PM

Hey

I have multiple tables, and I want to run a query on 2 tables in one

The DB's are set out like so

##DB 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)

## DB 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)

As you can see in both tables, I have prod_id
This is a number i enter and is relevant to another table holding the product name

I have put 2 examples in the tables, different informatiom, then try running this querry
SELECT stock.in_stock, prices.buy_price, prices.iva, prices.re, prices.total_price, prices.sell_price FROM products, stock, prices WHERE stock.prod_id = '3' AND prices.prod_id = '3'

This works ok
Except its returning 2 rows, Both the same data, the data I want which is relevant to prod_id being 3, but 2 rows

(I am using MySQL-Front to test the queries before building them in the script)

I know I can add LIMIT to the query, but will this query be ok when running against 1000 rows of data?



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 16 September 2006 - 11:23 PM

You're mising a join condition on the products table.
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