[freeside-devel] mysql and complex queries
Dave Burgess
burgess at neonramp.com
Tue Nov 20 10:31:04 PST 2001
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
More information about the freeside-devel
mailing list