SELECT from two tables not returning correct records.

[gazchap]

I've got two tables, users and profiles. The users table contains contact details of my users, and the profiles table contains a profile of a user, linked to the primary key of the users table.

Profiles are optional - if they have no profile, there is no record in the profiles table for them.

A (simplified) table layout for each table follows:

users: id (primary int(11)), username (varchar(20))

profiles: id (primary int (11)), user_id (int(11)), smoker (char(1))

Say I'm trying to do a search for all users who smoke.

My query for this is: SELECT DISTINCT users.id FROM users,profiles WHERE profiles.smoker='Y'

If I have two users, both with profile records, one of which smokes (smoker is set to Y) and one doesn't (smoker = N), this query returns both users.

I can get around this by adding AND users.id=profiles.user_id on to the query, but then people who have no profile are not returned.

What query could I use to search for users who smoke/do not smoke/have not specified whether they smoke (smoker is empty ("") in that case) and still return records for users with no record in the profiles table?

[pete_bisby]

You need a LEFT JOIN within your SQL query. This will select all records from the left-hand table (whichever is specified on the left-hand side of the join), and any information relating to the matching records. For example,

SELECT A.id, A.username, B.smoker FROM users A LEFT JOIN profiles B ON A.id=B.user_id

will display all records from the users table, and the smoker information whether the record exists or not from the profiles table (if the record doesn't exist in the profiles table, the value of the smoker attribute will be NULL).