[freeside-devel] mysql and complex queries

[Dave Burgess]

mysql> SELECT * FROM cust_main left outer join (
    ->   ( cust_pkg left outer join part_pkg using(pkgpart)
    ->   ) left outer join (
    ->     (
    ->       (
    ->         ( cust_svc left outer join part_svc using (svcpart)
    ->         ) left outer join (
    ->           svc_acct left outer join (
    ->             select svcnum, domain, catchall from svc_domain
    ->             ) as svc_acct_domsvc (
    ->               svc_acct_svcnum, svc_acct_domain, svc_acct_catchall

    ->           ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum
    ->         ) using (svcnum)
    ->       ) left outer join svc_domain using(svcnum)
    ->     ) left outer join svc_forward using(svcnum)
    ->   ) using (pkgnum)
    -> ) using (custnum)
    -> ;
ERROR 1064: You have an error in your SQL syntax near '(
  ( cust_pkg left outer join part_pkg using(pkgpart)
  ) l' at line 1
mysql>

Apparently not, but I haven't taken more than a couple of seconds with
it.

ivan wrote:

> can mysql handle this query?  if not, what would need to be changed?
>
> SELECT * FROM cust_main left outer join (
>   ( cust_pkg left outer join part_pkg using(pkgpart)
>   ) left outer join (
>     (
>       (
>         ( cust_svc left outer join part_svc using (svcpart)
>         ) left outer join (
>           svc_acct left outer join (
>             select svcnum, domain, catchall from svc_domain
>             ) as svc_acct_domsvc (
>               svc_acct_svcnum, svc_acct_domain, svc_acct_catchall
>           ) on svc_acct.domsvc = svc_acct_domsvc.svc_acct_svcnum
>         ) using (svcnum)
>       ) left outer join svc_domain using(svcnum)
>     ) left outer join svc_forward using(svcnum)
>   ) using (pkgnum)
> ) using (custnum)
>
>
> (apologies if the SQL indentation is weird)
>
> --
> _ivan