Simple DB join

[johnsmith153]

Joining two tables:

 

A simple users and photos table. They are connected by a user_id field.

 

(1) Users table.

 

(2) Photos (a 'profile_pic' field flags which photo is the profile picture)

 

There are lots of records in the photos table.

 

I want to simply return every user and display their profile picture (if they have one).

 

These are the issues I have:

 

(1) A simple join always returns the same number of records as there are photos, not one record per user (so if a user has 50 photos, I see the user record 50 times).

 

(2) If I use GROUP BY then I don;t see multiple records per user, but I don;t have any control over which record is selected from the Photos table.

 

(3) If I simply use:

 

WHERE profile_pic = '1'

 

...then if someone doesn;t have a profile pic I don;t see their record at all.

[requinix]

Use a LEFT JOIN and put the profile_pic=1 in the ON clause (not in the WHERE).