Hello, I need help with the an advanced SQL JOIN. I think that I can get this accomplished with a LEFT JOIN... perhaps some of you may be able to offer guidance in this... I appreciate any suggestions you may have.
Consider the following SQL statement: SELECT customerid, count(disctint count1) AS "TODAYS_USERS", count(distinct count2) AS "LAST 7 DAYS" , count(distinct count3) AS "LAST 30 DAYS" FROM (SELECT distinct case when modified >= '2010-02-11' then modelnumber else null end as count1, case when modified >= '2010-02-04' then modelnumber else null end as count2, case when modified >= '2010-01-11' then modelnumber else null end as count3, customerid FROM sales WHERE modelnumber LIKE 'GH77%') AS results GROUP BY results.customerid Now, the above SQL query retrieves results that will look something like this: customerid | TODAYS_USERS | LAST 7 DAYS | LAST 30 DAYS bigint | bigint | bigint | bigint ---------------------------------------------------------------------- 8699 | 1 | 1 | 1 8700 | 1 | 12 | 17 8701 | 3 | 5 | 19 Now... here is the problem I am having... the above SQL query is retrieving results from one table: sales I have another table called customers with a couple of fields (customerid, and customername are two of the fields). I want to join on the customerid in both tables to retrieve the customername in the query. So I need the results to look something like this: customerid | customername | TODAYS_USERS | LAST 7 DAYS | LAST 30 DAYS bigint | varchar | bigint | bigint | bigint -------------------------------------------------------------------------------------------------------------------------------- 8699 | Joe Smith | 1 | 1 | 1 8700 | Sara Olson | 1 | 12 | 17 8701 | Mike Jones | 3 | 5 | 19 Can someone show me how to use a JOIN with the above SQL Statement? I need to bring the customername field into the query from the other table and I have been having issues writting the query... can this even be done? Thanks, Neil