Query help please

Stelf

Hi all,

I've checked the MYSQL documents and I cannot seem to find the correct command/syntax to do what I want.

At the moment this query below will return the correct records, the field 'absent_auth_who' which is int(6) and carrys the numeric value of the user id, and this is linked to the 'user_id' in the 'user_dat' table where the users name is stored.

SELECT absent_master.*
FROM absent_master, user_dat
WHERE absent_started BETWEEN '2002-07-31' AND '2003-02-26' AND absent_master.absent_person = user_dat.user_id
ORDER BY absent_started ASC

But since not all records have been vetted, these unvetted records have the user_id of 0 I cannot use 'absent_master.absent_auth_who = user_dat.user_id' because it stops showing the records without a valid user attached to the 'absent_master.absent_auth_who' field.

I need to join the absent_auth_who & user_dat.user_id fields so I can obtain the persons name from the user_dat table based on the user id's.

I hope someone can help, hopefully I have explained well enough also. Thanks in advance.

eoghain

You want to do what is called an outer join, or left join.

SELECT absent_master.* 
  FROM absent_master LEFT JOIN user_dat ON absent_master.absent_person = user_dat.user_id
 WHERE absent_started BETWEEN '2002-07-31' AND '2003-02-26'
 ORDER BY absent_started ASC

This type of join brings you all records in the table on the right and populates the fields from the table on the left when there is a match otherwise it leaves them blank.