I am trying to return data, where I want the item name (items.name), vendor name (vendors.name), url of the price (prices.url), and the price (prices.price) from 3 different tables (items,vendors and prices) using the foreign key vid (vendor id) from prices.vid and vendors.vid and only showing results with item id 3 (items.iid='3'), yet I get results from different item id's as well.
Here is what the tables look like:
mysql> describe prices;
+-------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------+------+-----+---------+----------------+
| pid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| price | decimal(10,2) unsigned | YES | | NULL | |
| url | longtext | YES | | NULL | |
| iid | int(10) unsigned | NO | MUL | NULL | |
| vid | tinyint(3) unsigned | NO | MUL | NULL | |
+-------+------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from items;
| iid | name | scid | desc
| 1 | Ahtanum | 3 | Ahtanum is ...
| 2 | Amarillo | 3 | Amarillo is ...
| 3 | 22 oz Beer Bottles Amber | 56 | 22 ounce amber...
mysql> select * from prices;
+-----+-------+--------------------------------------------------------------------------------------+-----+-----+
| pid | price | url | iid | vid |
+-----+-------+--------------------------------------------------------------------------------------+-----+-----+
| 1 | 3.50 | homebrewhaven.com/id=10 | 2 | 4 |
| 2 | 4.50 | morebeer.com/id=15 | 2 | 7 |
| 3 | 11.99 | http://www.northernbrewer.com/default/beer-bottles-22-oz.html | 3 | 1 |
| 4 | 14.99 | http://www.austinhomebrew.com/product_info.php?products_id=993 | 3 | 2 |
| 5 | 12.50 | http://www.midwestsupplies.com/22-oz-bottles-amber-case-of-12.html | 3 | 3 |
| 6 | 14.50 | http://store.homebrewheaven.com/22-oz-brown-beer-bottles-case-of-12-bottles-p69.aspx | 3 | 4 |
| 7 | 12.95 | http://morebeer.com/view_product/15630//Bottles_-_22_oz_12 | 3 | 7 |
| 8 | 14.99 | http://www.brewmasterswarehouse.com/product/0101545/case-22-oz-beer-bottle-amber | 3 | 9 |
+-----+-------+--------------------------------------------------------------------------------------+-----+-----+
Now when I execute the query:
SELECT distinct items.name, vendors.name, prices.url, prices.price
FROM vendors, prices, items
WHERE vendors.vid = prices.vid AND items.iid='3'
ORDER BY prices.price;
I get also the price id's 1 & 2 in my result even though the iid (item id) are 2 and not 3 as I want in the query.... Output below:
mysql> select distinct items.name, vendors.name, prices.url, prices.price FROM vendors, prices, items WHERE vendors.vid = prices.vid AND items.iid='3' ORDER BY prices.price;
+--------------------------+-----------------------+--------------------------------------------------------------------------------------+-------+
| name | name | url | price |
+--------------------------+-----------------------+--------------------------------------------------------------------------------------+-------+
| 22 oz Beer Bottles Amber | Homebrew Heaven | homebrewhaven.com/id=10 | 3.50 |
| 22 oz Beer Bottles Amber | More Beer | morebeer.com/id=15 | 4.50 |
| 22 oz Beer Bottles Amber | Nothern Brewer | http://www.northernbrewer.com/default/beer-bottles-22-oz.html | 11.99 |
| 22 oz Beer Bottles Amber | Midwest Supplies | http://www.midwestsupplies.com/22-oz-bottles-amber-case-of-12.html | 12.50 |
| 22 oz Beer Bottles Amber | More Beer | http://morebeer.com/view_product/15630//Bottles_-_22_oz_12 | 12.95 |
| 22 oz Beer Bottles Amber | Homebrew Heaven | http://store.homebrewheaven.com/22-oz-brown-beer-bottles-case-of-12-bottles-p69.aspx | 14.50 |
| 22 oz Beer Bottles Amber | Austin Homebrew | http://www.austinhomebrew.com/product_info.php?products_id=993 | 14.99 |
| 22 oz Beer Bottles Amber | Brewmasters Warehouse | http://www.brewmasterswarehouse.com/product/0101545/case-22-oz-beer-bottle-amber | 14.99 |
+--------------------------+-----------------------+--------------------------------------------------------------------------------------+-------+
8 rows in set (0.04 sec)
As you can see, I am getting the first two results which have iid = 2, not 3. All the rest should appear, but not those first two.
Any ideas as to whats going on?