SQL query problem (with screenshot)

advertisements

I have three tables:

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

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>

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:

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.


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


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

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.