1

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?

Improve this question
3
  • 1
    Your items table is not correctly joined to the others. Rewrite your query using ANSI syntax and your problem will become clearer.
    – Nick Chammas
    Sep 14, 2011 at 16:45
  • 1
    For example: SELECT DISTINCT i.name , v.name , p.url , p.price FROM vendors v INNER JOIN prices p ON v.vid = p.vid INNER JOIN items i ON p.iid = i.iid WHERE i.iid='3' ORDER BY p.price;
    – Nick Chammas
    Sep 14, 2011 at 16:49
  • 2
    This really should have been asked on Stack Overflow
    – jcolebrand ♦
    Sep 14, 2011 at 18:41

1 Answer 1

Reset to default
5

You are missing a JOIN between prices and items.

Your implicit join in the where clause only joins vendors and prices so you get a cartesian join for the result of that join and the items table. If you had used ANSI JOIN keywords instead, you would have noticed that immediately

You need something like this:

select distinct items.name, 
       vendors.name, 
       prices.url, 
       prices.price 
 FROM vendors
   JOIN prices ON vendors.vid = prices.vid 
   JOIN items ON items.iid = prices.iid ---  this part is missing
 WHERE items.iid='3' 
ORDER BY prices.price;

I don't know if the join condition is correct, because you didn't tell us which column in prices contains the foreign key to items.

Improve this answer
1
  • @jgilmour If it works you can accept the answer by clicking the check mark next to it. This indicates to future visitors that it is the correct solution.
    – JNK
    May 29, 2012 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.