JOIN

It's hard to tell exactly what you are doing w/out knowing what the tables look like and the whole query.

I've run multiple joins w/no problems though I've not tried to do so w/aliases. I just tried running one and it does look like my database may be croaking on me. Try it just using proper table names and/or post more info.

Hi dwhalen,

In this article you will find an example of using JOIN taken from the link betow.


********************************************
Indexing for JOINs and FOREIGN KEYs

Tip #6: Always index all fields on which you expect to regularly JOIN, on both sides of the JOIN.



Tip #7: Index all Foreign Keys.



You'll notice that the three main tables of Policy Pages are a straightforward hierarchical structure, with a one-to-many relationship between policy_cats and policy_pages, and a one-to-many relationship between policy_pages and policy_items. You will also notice, though, that while PostgreSQL automatically creates an index for each primary key, no such index is created for the two Foreign Keys, policy_pages.pcat_id and policy_items.ppage_id. Time to fix that:

CREATE INDEX idx_ppage_pcat ON policy_pages(pcat_id);
CREATE INDEX idx_pitem_ppage ON policy_items(ppage_id);

This has two advantages: First, practically every query against the Policy Pages application contains a two-table join like this:

SELECT pcat_id, pcat_name, count(ppage_id) as no_pages
FROM policy_cats JOIN policy_pages USING (pcat_id)
WHERE policy_cats.status > 0
GROUP BY pcat_id, pcat_name;

Having both policy_cats.pcat_id and policy_pages.pcat_id indexed allows PostgreSQL to use an Index Scan when joining the two tables, which can speed things up considerably for large tables. It helps even more when the JOIN must be done on the client side, such as a form/subform display of records.

Second, indexing the Foreign Key can actually speed up data edits on the parent table. Whenever you update or delete a record in a parent table, PostgreSQL executes one or more triggers to verify that the query statement does not violate the referential integrity rules you have set up. These triggers must scan the Foreign Key columns of the child tables, an operation which indexes improve.

******************************************************

LelandJ Leland F. Jackson, CPA
Software - Master (TM)
Nothing Runs Like the Fox