Hi,
I'm doing some complicated joining and am getting error
messages about unknown relations and can't figure out
what's up. I'm wondering if "as" aliasing gives
an alias to the product of a join, not just the
one table that appears immediately in front of the
"as". ?
Rather than try to describe the database design
I don't suppose there's a reference database that's
used for regression testing or something that
I might be able to use to describe the problem?
I have documentation, but there'd be a lot of reading
and thinking about the problem domain for somebody
else to jump into. What's the right way to ask
for help about something like this? I'd be happy
to post my giant query.
Let's see if I can summarize.
I join table B to table C and constrain C's rows
by requiring a column be constant. This gives
me a subset of B. I do this 3 times, for 3
subsets. Call them B1, B2, and B3. Each
row of the B subsets contains a key, this
key may appear in 0 or 1 rows of each of the
other B subsets. I want to do a self join
so I get rows that look like:
B1.x, B1.y, B2.x, B2.y, B3.x, B3.y
An outer join is necessary as any of B1,
B2, or B3 may not exist, in which case I want
NULL values in those cells of the output grid. Meanwhile
table A left outer joins to tables B1,
B2, and B3 to further constrain the output.
I can do this, I think, if I only want to make
the subsets B1 and B2, but when I add a third
I loose it. I think because at that point I start
to need parenthesis around the join clauses.
I could always make temporary tables B1, B2, and B3.
This might even be more sane, if not more efficent.
But I'm dissappointed that my SQL-fu is not up
to the task of doing it all in a single statement.
I'm unclear about the interactions between JOINs
and "AS", about the effects of parenthesis in join
clauses, whether "AS" has any scope, the implications
of using commas in from clauses especially regards
whether tables in other "from_items" can be used
within a different "from_item's" "JOIN ON".
I guess in general I'm wondering about scope,
whether it exists and what it is if it does.
There must be some scope as my giant SQL statement
says "as foo" and I get compaints that foo does not
exist. Or there's something else I just don't get
about using JOIN in FROM clauses.
The other question that sprang to mind while working
on this was whether there's performance (or any other)
implications of using WHERE conditions instead of
JOIN inside FROM.
Thanks.
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
