left join with where clause OUT OF IDEAS

laurus

I GIVE UP!!

This is a Mysql database.

All I am trying to do is get all the site names per affil_id (affiliate)
if there are values in the stats table for that site on a variable day show them, if not just show NULL or anything (but return the site name for sure)

Here are my tables:

sites table

affil_id - site_name (col. names)
43 - Joes Site
43 - Jims Site
43 - Jerrys Site
43 - Jonahs Site

stats table

affiliate_id - site_name - p_date (col. names)
43 - Joes Site - 2007-08-15
43 - Jims Site - 2007-08-15

Here is the code:

select st.affil_id, st.site_name,   s.p_date 
from sites st

left join stats s on s.site_name  = st.site_name 

where st.affil_id = '43' and (s.p_date = '2007-08-16' or s.p_date is NULL)

It works great on the 15th! brings back 4 records BUT when I try a date that has no matches in the stats table it only brings back 2 records (Jerrys and Jonahs, the 2 that aren't in the stats table at all)

thanks in advance.

rulian

Left joins are dependent on their parent tables, so if there are no matches on the parent table left joins wont return records.
look here:
http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html
examples of how left, right, inner, outter differ

that's not to say that's exactly the problem you might be having, but it's worth trying different approaches