[RESOLVED] PHP and SQL Help

[Sayian]

Hello all , im having a problem with my code

What this does , is get's username's along with the user's picture from my database with different criteria.

The users images are stored on disk , not in the database , but the photo filenames are stored in the database

The only was i have found to be able to get these photo filenames without running a seperate query within this code , is to join the shocc_account_photos table , and get the photofilename where the username's match.

But what about if a user doesnt have a picture? id still like to return information on them , but with this current code , only user's with pics are being returned b/c thats the only way i know i can get the photo filename for the users who HAVE pictures , but at the same time , this excludes the user's who dont. So they wont even come up in the search

 $sql = "SELECT shocc_users.city, shocc_users.state, shocc_users.username,
shocc_users.rating,shocc_users.age,shocc_users.ethnicity,shocc_users.gender,shocc_users.starsign,
shocc_account_photos.photo_filename,shocc_active.time
FROM shocc_users
JOIN shocc_active
ON shocc_users.username = shocc_active.username
JOIN shocc_account_photos
ON shocc_users.username = shocc_account_photos.username
WHERE main=1 // Main =1 is the Users MAIN picture
ORDER BY rand()

Is their anyway i can change this , to be able to get the users photo_filename if they have a pic , but still return user's who dont?

[mouloud2001]

you sould try LEFT JOIN. And even NATURAL LEFT JOIN, as the two fields have the same name.
So just :
shocc_users NATURAL LEFT JOIN shocc_active
without ON clause.
I't won't change anything, but you sould use aliases :
SELECT u.city, u.state, u.username,
u.rating,u.age,u.ethnicity,u.gender,u.starsign,
p.photo_filename,a.time
FROM shocc_users u
NATURAL LEFT JOIN shocc_active a
NATURAL LEFT JOIN shocc_account_photos p
WHERE main=1
ORDER BY rand()