I was wondering if anyone could help me converting some PostgreSQL
queries with sub-queries into a similar form usable (hopefully) by both,
or at least into a form that I can use in place of the PostgreSQL ones.
I'm going to post them - I hope no one minds. If this is the wrong
mailing list for these kinds of questions, I apologize.
I'm currently using the latest Version 3.23.43, but I can change to
Version 4 if these queries are supported there. If it matters, these
are for the new version of Freeside, an ISP billing program that
(reluctantly) has supported transaction-safe (InnoDB, in my case) tables
in MySQL. I think the first one lists all of the customers that have
uncancelled packages.
Here they are:
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
)
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
)
)
SELECT * FROM svc_acct
WHERE 0 <
( SELECT count(*) FROM cust_svc
WHERE cust_svc.svcnum = svc_acct.svcnum
AND pkgnum IS NULL
)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
