SQL查询问题(附截图) [英] SQL query issue (with screenshot)

问题描述

我有三张桌子:

Shop
Shop_id  City_id   Address  
1        1         Address 1      
2        1         Address 2
3        2         Address 3 
4        2         Address 4
5        1         Address 5
6        1         Address 6

City
City_id  Name
1        Vilnius
2        Kaunas

Places
Place_id  Shop_id  Book_id
1         3        1
2         2        1
3         3        2
4         4        3
5         3        3

在 Places 表中，我保存了可以购买特定书籍的商店.我也有复选框形式:

In Places table I save shops where you can buy specific book. Also I have checkboxes form:

$result = mysql_query("SELECT places.place_id, places.book_id, places.shop_id,
shop.shop_id, shop.city_id,shop.address,city.city_id,city.name 
FROM shop INNER JOIN places ON places.shop_id=shop.shop_id 
INNER JOIN city ON shop.city_id=city.city_id") or die(mysql_error());

if(mysql_num_rows($result) > 0) {
    while($row = mysql_fetch_assoc($result2)) {
        echo '<tr> 
                <td><input type="checkbox"'; if ($row['book_id']==$id2){echo 'checked';}echo' name="identifer[]" value="'.$row['shop_id'].'" /> <br /></td>  
                <td>'.ucfirst($row['Name']).','.$row['Address'].' </td> 
             </tr> 

如果Book_id 等于$id2，我正在尝试使用所有 可用商店创建表格，并勾选复选框.为了更清楚这里是截图，它显示了我从我的代码中得到的:

I'm trying to create table with all available shops with checbox checked value if Book_id equal to $id2. To be more clear here is the screenshot, it shows what I get from my code:

显然，它基本上从Places 表中记下所有商店.我正在尝试更改我的 SQL 代码，但我无法正确更改，所以我需要帮助.

Obviously, it basically write down all shops from Places table. I'm trying to change my SQL code, but I can't get it right, so I need help.

推荐答案

我猜你的问题不能用 LEFT JOIN 解决，因为那样你可以得到多个 <每个 Shop 的 code>Places.试试这个:

I guess your problem is not solved with a LEFT JOIN, because then you can get multiple Places per Shop. Try this:

SELECT EXISTS (SELECT * FROM places p
               WHERE  p.shop_id = s.shop_id AND p.Book_id = $id2) AS has_book
      ,s.shop_id, s.city_id, s.address
      ,c.city_id, c.name 
FROM   shop s
JOIN   city c USING (city_id)  -- or LEFT JOIN if city could be missing

<小时>

或者，如果 (Shop_id, Book_id) 保证是唯一的 - 意味着一本书在商店中永远不会出现超过一次 - 并且你想包括列从 Places，你可以使用这样的查询:

---

Or, if (Shop_id, Book_id) is guaranteed to be unique - meaning a book can never appear more than once in a shop - and you want to include columns from Places, you can use a query like this:

SELECT p.place_id, p.book_id, p.shop_id
      ,s.shop_id, s.city_id, s.address
      ,c.city_id, c.name 
FROM   shop s
JOIN   city c USING (city_id)  -- or LEFT JOIN if city could be missing
LEFT   JOIN places p ON p.shop_id = s.shop_id AND p.Book_id = $id2

注意LEFT JOIN 的ON 子句中的附加条件.必须在那里，而不是在 WHERE 子句中.这会为您提供每家 商店，并且仅 为有书的地方附加数据.由于一家商店每本书只能有一次，没有商店会翻倍.

Note the additional condition in the ON clause of the LEFT JOIN. Must be there, not in a WHERE clause. That gives you every shop and appends data for a place only if it has the book. As a shop can have every book only once, no shop will be doubled.

这篇关于SQL查询问题(附截图)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！