Re: SQL help - Mailing list pgsql-novice

Chad,

> > >
> > Hmmm... you're saying that you want all lists for a client if their
> > projects have no specific lists, but the lists for the projects if
> they
> > do?  Or something else?
>
> Yes, thats it exactly.

This answer below.

> If it doesnt use the inner join syntax, then what would i use in
> place?
> "inner join" works just fine for my queries.

Didn't know the term was supported.  In Postgres, you can just use
"JOIN" and save yourself some typing.

> Im using 7.2.0

You should upgrade to 7.2.3 at least.   7.2.0 has several significant
bugs you may find the hard way, not the least of which are broken
crytpo for secure authentication and some major pg_dump bugs.

> Ive always understood inner join to be faster than
>
> select col1, col2
> from tbl1,tbl1
> where tbl1.col1 = tbl2.col2

On the contrary:  If you are not a query expert, the above syntax (the
"SQL89 JOIN") gives the query parser a freer hand to decide JOIN order
and possibly make your query more efficient.  Explicit JOINs limit the
parser's query choices and can result in very inefficient queries if
you do them badly.

That being said, it probably doesn't make a difference in your case, as
JOIN order does not cause a large performance variation in small
databases.

Now, your query.

--You want to list all lists with a project
select c.id as client_id, p.id as project_id, l.id as list_of_lists_id
from clients c
join projects p on c.id = p.client_id
join list_of_lists l on p.id = l.project_id
--together with all lists for clients with list-less projects
UNION ALL
select c.id as client_id, p.id as project_id, l.id as list_of_lists_id
from clients c
join list_of_lists l on c.id = l.client_id
join projects p on c.id = p.client_id  
where not exists (select p2.id
    FROM projects p2 JOIN list_of_lists l2
        ON p2.id = l2.project_id
        where p2.client_id = c.id)
ORDER BY ???
(above SQL *not* tested for errors)

This should give you a list of all clients, their projects and their
lists for clients all of whose projects don't have lists, combined with
a list of clients, and their listed projects for clients where any of
their projects have lists.
What it will not show you is list-less projects of clients who have
other projects with lists, which could be done, but would be a
different query.  It will also have the defect of showing you the
cartesian product of clients with multiple projects and lists which
have no association, i.e. if client 4 has 3 (list-less) projects and 2
lists then you'll get 6 rows.

Overall, I have to wonder at your schema.  The fact that a list can be
attached to either a client or a project, or both, is going to cause
you *considerable* difficulty in the future, and should be changed if
there is still time.  While I have designed databases with this kind of
relational ambiguity before, it was only after acquiring 5 years of DBA
experience, as such databases are very hard to manage.

You should consider picking up a copy of Pascal's "Practical Issues in
Database Management" to learn about normal form and why it's a good
thing.

-Josh Berkus