[freeside-devel] mysql and complex queries

[Dave Burgess]

OK.

I've been through the BNF for the SELECT statement in MySQL, and Version
3 doesn't like this particular syntax at all.

- The parentheses might be a problem.  I wasn't able to use them for
grouping.
- Even without those, cascading OUTER JOINS need to have a specific
syntax:

  SELECT * FROM table LEFT OUTER JOIN table_reference USING (field_list)
LEFT OUTER JOIN table_reference ON conditional ;

USING and ON clauses can be interchanged.

So, the query below is OK.

SELECT * FROM cust_main left outer join cust_pkg using (custnum) left
outer join part_pkg using (pkgpart);

This one fails:

SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
    left outer join part_pkg using (pkgpart)
      left outer join cust_svc

It fails on the final "LEFT OUTER JOIN", apparently because there's no
obvious way to connect these tables....

If you don't mind me asking, is this just a thought exercise, or are we
actually trying to accomplish something in particular.  For example, I
don't see how we can connect part_pkg and cust_svc like this last join
is trying.

If I modify the select so that it tries to connect the cust_svc.pkgnum
key with the one in the cust_pkg (like this):

SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
    left outer join part_pkg using (pkgpart)
      left outer join cust_svc using (pkgnum)

It fails again (ERROR 1054: Unknown column 'part_pkg.pkgnum' in 'on
clause')

If I replace the using(pkgnum) with "on cust_svc.pkgnum =
cust_pkg.pkgnum" the SELECT works again.

SELECT * FROM cust_main left outer join cust_pkg using  (custnum)
    left outer join part_pkg using (pkgpart)
      left outer join cust_svc on cust_svc.pkgnum = cust_pkg.pkgnum

So, complex LEFT JOINS are possible, it might just take a little more
study to get the query to work in MySQL.

BTW:  MySQL Version 4 supports UNION now, so we might be able to use
that to accomplish this.


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