简单的select语句不返回正确的数据


1

我想返回数据,我想要的项目名称(items.name),供应商名称(vendors.name),价格的网址(prices.url)和价格(items.iid ='3')使用来自prices.vid和vendors.vid的外键vid(供应商ID)从3个不同的表格(物品,供应商和价格)获得价格(价格。价格),并且仅显示物品ID为3的结果(items.iid ='3') ,但我也从不同的项目ID获得结果。

下面是桌子的样子:

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 | 
+-----+-------+--------------------------------------------------------------------------------------+-----+-----+ 

现在,当我执行查询:

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; 

我得到同样的价格ID的1 & 2在我的结果,即使IID(项目ID)是2,而不是3,因为我想在查询....输出如下:

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) 

当你可以看到,我得到的前两个结果有iid = 2,而不是3.其余所有应该出现,但不是前两个。

关于发生了什么事情的任何想法?

+1

您的商品表格未正确连接到其他商品。用ANSI语法重写你的查询,你的问题就会变得更加清晰。 14 9月. 112011-09-14 16:45:08

+1

例如: SELECT DISTINCT i.name ,v.name ,p.url ,p.price FROM 厂商v INNER JOIN价格p ON v.vid = p.vid INNER JOIN项i ON p.iid = i.iid WHERE i.iid ='3' ORDER BY p.price; 14 9月. 112011-09-14 16:49:52

  0

它的工作原理!谢谢! 14 9月. 112011-09-14 17:04:02

+2

这真的应该被问到[so] 14 9月. 112011-09-14 18:41:26

5

您缺少价格和物品之间的联接。

您在where子句中的隐式连接只会连接供应商和价格,因此您可以对该连接和项目表的结果进行笛卡尔连接。 如果你曾经使用ANSI JOIN关键字相反,你会注意到,立即

你需要的东西是这样的:

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; 

我不知道,如果连接条件是正确的,因为你没有告诉我们价格中的哪一列包含项目的外键。

  0

它的工作原理!谢谢! 14 9月. 112011-09-14 17:04:11

  0

@jgilmour如果有效,您可以点击旁边的复选标记来接受答案。这表明未来的访问者认为这是正确的解决方案。 29 5月. 122012-05-29 10:33:25