[freeside-devel] 1.4.0pre6

[Dave Burgess]

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?

>
> >
> > 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 which relate to part packages and have unused services.

> >
> > 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;