Worse, I've just run across:Looks kind of like a star schema(1) to me, with the EMPLOYER table at the center of everything. OK, sligthly "snowflakey"(1) in a couple of places, where BROKER is linked in via SETOFEMPLOYERS(2) and BILLINGCONTACT via EMPLOYERMISCINFO(2). Like this:
FROM EMPLOYER , BILLINGUPCOMINGINVMANUALADJ, EMPLOYERMISCINFO, BILLINGCONTACT ,
ROLE, BROKER, SETOFEMPLOYERS,
(SELECT EMPLOYERID, COUNT(*) AS ACTIVEEENO FROM EMPLOYMENT WHERE EMPLOYMENT.STATUS <> 1
GROUP BY EMPLOYERID HAVING COUNT(*) > 0) AS ACTIVEEES
WHERE EMPLOYER.FEDERALTAXID = EMPLOYERMISCINFO.EMPLOYERID
AND EMPLOYER.FEDERALTAXID = SETOFEMPLOYERS.EMPLOYERTAXID
AND BROKER.BROKERID = SETOFEMPLOYERS.BROKERID
AND EMPLOYERMISCINFO.PAYER = BILLINGCONTACT.CONTACTPAYER(+)
AND ROLE.ID = EMPLOYER.FEDERALTAXID
AND ROLE.FLAG = 1 AND ROLE.ROLE = 8
AND EMPLOYERMISCINFO.EMPLOYERID = BILLINGCONTACT.CONTACTID(+)
AND EMPLOYER.FEDERALTAXID = BILLINGUPCOMINGINVMANUALADJ.EMPLOYERID(+)
AND EMPLOYER.FEDERALTAXID = ACTIVEEES.EMPLOYERID(+)
AND EMPLOYER.FEDERALTAXID IN (SELECT EMPLOYERID FROM PineyCreek.EMPLOYERSTOBEBILLED)
ORDER BY EMPLOYER.FEDERALTAXID
I have no idea where to start with such a complex join. (Other people's) SQL is hard.
+------------------+\n | BILLINGUPCOMING |\n | INVMANUALADJ |\n +------------------+\n |\n+---------+ +-----------------------+ +---------+\n| ROLE |---| EMPLOYER |---|SELECT...|\n+---------+ +-----------------------+ +---------+\n | |\n +---------+ +---------+\n | SETOF | |EMPLOYER |\n |EMPLOYERS| |MISCINFO |\n +---------+ +---------+\n | |\n +---------+ +---------+\n | BROKER | | BILLING |\n +---------+ | CONTACT |\n +---------+(Disclaimer: AFAICS, at a quick glance.)
In my experience, the trick (well, not much of a "trick" really, but still...) is, just look at which table name appears most often in the joining-subclauses of the WHERE clause, and unravel it from there; next thing you look for is, which subclauses *doesn't* that table name appear in, then you know one of the tables in that subclause is indirectly joined to the "central-master" table.
HTH!
(1): For those who haven't seen this before, "star" and "snowflake" are data warehousing terminology.
(2): Nice of them to have one of those one way around, and the other the other, BTW. Hey, we don't want to make this too easy to read, so just any schmuck could understand it, now do we? ;-)