[freeside-devel] 1.4.0pre6

[ivan]

On Tue, Dec 04, 2001 at 07:57:48AM -0600, Dave Burgess wrote:
> Dave Burgess wrote:
> 
> > ivan wrote:
> >
> >
> > These subselects are not working.
> >
> > I'll get back to you on the mysql syntax for these selects.
> >
> 
> I'm back.
> 
> >
> > >
> > > SELECT * FROM cust_main
> > >       WHERE 0 < ( SELECT COUNT(*) FROM cust_pkg
> > >                     WHERE cust_pkg.custnum = cust_main.custnum
> > >                       AND ( cust_pkg.cancel IS NULL
> > >                             OR cust_pkg.cancel = 0
> > >                           )
> > >                 )
> > >          OR 0 = ( SELECT COUNT(*) FROM cust_pkg
> > >                     WHERE cust_pkg.custnum = cust_main.custnum
> > >                 )
> 
> This one I can do with JOIN, I just haven't figured out how yet.
> 
> Does this one translate into:
> 
> Select all customer records which do not have any active packages?

Yes.

> > > SELECT * FROM cust_pkg
> > >     WHERE 0 <
> > >       ( SELECT count(*) FROM pkg_svc
> > >           WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
> > >             AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
> > >                                        WHERE cust_svc.pkgnum = cust_pkg.pkgnum
> > >                                          AND cust_svc.svcpart = pkg_svc.svcpart
> > >                                    )
> > >       )
> >
> 
> This one is scarier, but I think it's doable as well.
> 
> If I understand, this one translates to:
> 
> select all customer packages

Yes.

> which relate to part packages

?

> and have unused services.

Yes.

> > > SELECT * FROM svc_acct
> > >     WHERE 0 <
> > >       ( SELECT count(*) FROM cust_svc
> > >           WHERE cust_svc.svcnum = svc_acct.svcnum
> > >             AND pkgnum IS NULL
> > >       )
> > >
> 
> Is this the same?
> 
> select svc_acct.* from svc_acct, cust_svc
>   where cust_svc.pkgnum is null and cust_svc.svcnum = svc_acct.svcnum;

Hmm, the query is equivalent, but httemplate/search/svc_acct.cgi will need
some work to do it... 

-- 
_ivan