Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 '11 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 '11 at 16:49
    
It works! Thanks! –  jgilmour Sep 14 '11 at 17:04
2  
This really should have been asked on Stack Overflow –  jcolebrand Sep 14 '11 at 18:41
add comment

1 Answer

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.

share|improve this answer
    
It works! Thanks! –  jgilmour Sep 14 '11 at 17:04
    
@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 '12 at 10:33
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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