Thread: help with SQL join

help with SQL join

From

Neil Stlyz

Date:

11 February 2010, 17:46:19

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

Re: help with SQL join

From

John R Pierce

Date:

11 February 2010, 19:01:42

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

Re: help with SQL join

From

"Igor Neyman"

Date:

12 February 2010, 12:46:07


> -----Original Message-----
> From: John R Pierce [mailto:pierce@hogranch.com]
> Sent: Thursday, February 11, 2010 3:01 PM
> To: pgsql-general@postgresql.org
> Subject: 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
>


One correction:  you should "group" on all non-aggregate columns in your
"select" list, i.e.:

 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, c.customername

Igor Neyman