SQL query issue (with screenshot)

Question

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.

Answer 1

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.

Answer 2

You're doing an INNER JOIN on both tables. That means the query will return records where there's data on BOTH sides of the join only. Your Places table lists only shops 2,3,4. So records with ID 1,5,6 in the Shops table will not be returned, because there's no matching Places record.

Answer 3

Try replacing your INNER JOINS with LEFT JOINS

Answer 4

Why don't you pass query of id2 into your query? If am understanding your question properly:

$query = sprintf("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
AND places.Place_id='%s'",mysql_real_escape_string($id2));

$result = mysql_query($query);

Answer 5

Shop LEFT JOIN Places LEFT JOIN City and GROUP BY shop_id.