MySQL JOIN to get available photos
So here's my problem. I have two tables, `photos` and `users_homepage_photos`. In my site users need to be able to pick from their uploaded photos and tick them so they appear on their site homepage.

The `photos` table stores all the photo data (image URL, name, caption, etc.), and the `users_homepage_photos` table is a cross-reference table comprised of `id`, `user_id`, and `photo_id`.

**I want to write a query that will get all the photos *available* for use on the homepage**, therefore selecting all photos that are NOT already in the `users_homepage_photos` table.

My query so far is this (with `photos.user_id` being 4 as my test user ID):

    SELECT photos.id, photos.user_id, photos.s3_thumb, photos.`name`, photos.`date`, photos.`caption`,
    	users_homepage_photos.*
    FROM photos
    JOIN users_homepage_photos ON users_homepage_photos.`photo_id` != photos.id AND users_homepage_photos.`user_id` = 4
    WHERE photos.user_id = 4

**Here's the problem:** - when the user has 1 photo already in the `users_homepage_photos` table, the query works perfectly and selects all photos except for that photo already in there.

**However** if the user has NO photos on their homepage, the query selects 0 rows. **How can I get it to select all the rows in this case?**

Thanks!

Jack
MySQL JOIN to get available photos
===

So here's my problem. I have two tables, `photos` and `users_homepage_photos`. In my site users need to be able to pick from their uploaded photos and tick them so they appear on their site homepage.

The `photos` table stores all the photo data (image URL, name, caption, etc.), and the `users_homepage_photos` table is a cross-reference table comprised of `id`, `user_id`, and `photo_id`.

**I want to write a query that will get all the photos *available* for use on the homepage**, therefore selecting all photos that are NOT already in the `users_homepage_photos` table.

My query so far is this (with `photos.user_id` being 4 as my test user ID):

    SELECT photos.id, photos.user_id, photos.s3_thumb, photos.`name`, photos.`date`, photos.`caption`,
    	users_homepage_photos.*
    FROM photos
    JOIN users_homepage_photos ON users_homepage_photos.`photo_id` != photos.id AND users_homepage_photos.`user_id` = 4
    WHERE photos.user_id = 4

**Here's the problem:** - when the user has 1 photo already in the `users_homepage_photos` table, the query works perfectly and selects all photos except for that photo already in there.

**However** if the user has NO photos on their homepage, the query selects 0 rows. **How can I get it to select all the rows in this case?**

Thanks!

Jack