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