Re: help with SQL join

Neil Stlyz wrote:
> 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?

something like...

SELECT results.customerid, c.customername, count(distinct count1) AS
"TODAYS_USERS",
count(distinct count2) AS "LAST 7 DAYS" ,
count(distinct count3) AS "LAST 30 DAYS"
FROM (SELECT distinct case when s.modified >= '2010-02-11' then
s.modelnumber else null end as count1,
case when s.modified >= '2010-02-04' then s.modelnumber else null
end as count2,
case when s.modified >= '2010-01-11' then s.modelnumber else null
end as count3, s.customerid
FROM sales as s WHERE s.modelnumber LIKE 'GH77%') AS results
JOIN customers as c ON (results.customerid = c.customerid)
GROUP BY results.customerid