SQL Code - Join

[zerohaze07]

I have a solution for the following however my solution uses two queries. I need this to work in one sql statement. I believe i'm suppose to use a 'Join' however i cant get it to work.

 

Below is my SQL statement, this sql query works but not the way needed.

 

Select * from ahsg_directory, ahsg_subcategory, ahsg_directory_category, ahsg_directory_location where dir_id = dirc_dir_id and dirc_sub_id = sub_id and sub_id = '3' and (dir_id = sloc_dir_id and sloc_default = 1)

 

A shortened Example of my Database is below

-------------------------------------------

 

Table: ahsg_Directory : Fields (dir_id,dir_name)

Table: ahsg_subcategory : Fields (sub_id,sub_name,sub_main_id)

Table: ahsg_directory_category : Fields (dirc_id,dirc_dir_id,dirc_sub_id)

Table: ahsg_Directory_location : Fields (sloc_id,sloc_dir_id,sloc_phone,sloc_fax,sloc_default)

 

-------------------------------------------

 

My problem is that not every record contains information in the 'ahsg_directory_location' table. As my SQL statement includes in my where clause the condition "... and (dir_id = sloc_dir_id and sloc_default = 1)" only records containg info in that table is returned. I need all the records that meet the initial where clause returned with a null or blank value for 'sloc_phone' if there is no record in that table along with the records which have info in that table.

 

My script is a page showing listings 10 per page, so it loops through the record set, In my solution i simple deleted the portion relating to the location table and in each loop i carry out a second sql statement that checks the location table. So all up im executing 11 sql statements.

[btherl]

What you need is a "left outer join", or "left join" for short.  That means "Return rows from the left table even if there's none in the right table".

 

Here is the whole statement in join style:

 

Select * from ahsg_directory
JOIN ahsg_directory_category ON (dir_id = dirc_dir_id)
JOIN ahsg_subcategory ON (dirc_sub_id = sub_id)
LEFT JOIN ahsg_directory_location ON (dir_id = sloc_dir_id)
WHERE sub_id = '3' and (sloc_default = 1 OR sloc_default IS NULL)

 

I added another condition "sloc_default IS NULL" which will let you get rows where the left join produced nulls in that table.