Re: SQL help - Mailing list pgsql-novice

Thank you so much for the help.  The reason for this query is to make a
table that I will be able to use to move/add lists to different projects.
The relationship between clients and projects is always set.  But a list may
be in more than one project regardless of who owns it.  I am trying to give
flexibility to the lists, not to their clients.  I simply thought that
adding the client id to this table might make it easier to reference.  I can
see however that it could get me into trouble as well.

----- Original Message -----
From: "Josh Berkus" <josh@agliodbs.com>
To: "Chad Thompson" <chad@weblinkservices.com>; "pgsql-novice"
<pgsql-novice@postgresql.org>
Sent: Sunday, December 15, 2002 2:01 PM
Subject: Re: [NOVICE] SQL help


> 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
>
>