Thread: SQL help

Chad,

> I have a problem that I know can be solved if I can only explain it
> properly.

<grin> Personally, I frequently find that as soon as I explain a
problem clearly, the answer becomes self-evident.

> I have 3 tables, clients, projects and list_of_lists. Each has an Id
> field
>
> select c.id as client_id, p.id as project_id, l.id as
> list_of_lists_id
> from clients c inner join list_of_lists l on c.id = l.client_id
> inner join projects p on c.id = p.client_id
>
> This gives me every client, their projects and every list.  I want
> most of this but not all.
>
> select c.id as client_id, p.id as project_id, l.id as
> list_of_lists_id
> from clients c inner join projects p on c.id = p.client_id
> inner join list_of_lists l on p.id = l.project_id
>
> This gives me only those lists that are associated with specific
> projects.
> If you havent guessed already Im trying to recover from bad database
> design.
>
> So what Im looking for is a list, like that of the first query, but
> if a project only has specific lists associated with it, I want only
> those from the second query.

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?

And, btw, Postgres doesn't use the "inner join" syntax.  Are you sure
that you're using PostgreSQL?

-Josh Berkus

> <grin> Personally, I frequently find that as soon as I explain a
> problem clearly, the answer becomes self-evident.

Thats exactly what I was hoping for.. but to no avail.

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

>
> And, btw, Postgres doesn't use the "inner join" syntax.  Are you sure
> that you're using PostgreSQL?
>

If it doesnt use the inner join syntax, then what would i use in place?
"inner join" works just fine for my queries.
Im using 7.2.0
Ive always understood inner join to be faster than

select col1, col2
from tbl1,tbl1
where tbl1.col1 = tbl2.col2

But my skill doesnt go far beyond that.

Thanks
Chad

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

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