joining 4 tables

Dear,

I've got 4 tables:

articles, users, categories and comments. Each table has a primary key:
art_id, user_id, cat_id and com_id.

The relations are simple:
1 article is written by 1 user and has 1 category. An article can have
many comments.

I want a query that gives me the article's subject, date, summary, the
username and the number of comments.


SELECT a.art_id, a.title, a.date, a.summary, u.name
FROM articles a
LEFT JOIN users u USING (user_id)
gives me all the correct items so far, but when I want to join another
table I'm stuck.

How can I join the other 2 tables? The error-message I get is
Unknown column 'db.u.cat_id' in 'on clause' when I join the
'categories'-table.

> articles, users, categories and comments. Each table has a primary key:
> art_id, user_id, cat_id and com_id.

OK

> I want a query that gives me the article's subject, date, summary, the
> username and the number of comments.

I would try something like this (not tested) :

SELECT
a.art_id, a.title, a.date, a.summary,
b.name,
c.category,
COUNT(d.comm_id) AS ncomments
FROM articles a
LEFT JOIN users b ON (a.user_id = b.user_id)
LEFT JOIN categories c ON (a.cat_id = c.cat_id)
LEFT JOIN comments d ON (a.art_id = d.art_id)
GROUP BY a.art_id

Without the GROUP BY it would return as many n rows when there are n
comments (n > 0). I hope that it doesn't fail with this GROUP BY clause.

Bert Melis wrote:
> Dear,
>
> I've got 4 tables:
>
> articles, users, categories and comments. Each table has a primary key:
> art_id, user_id, cat_id and com_id.
>
> The relations are simple:
> 1 article is written by 1 user and has 1 category. An article can have
> many comments.
>
> I want a query that gives me the article's subject, date, summary, the
> username and the number of comments.
>
>
> SELECT a.art_id, a.title, a.date, a.summary, u.name
> FROM articles a
> LEFT JOIN users u USING (user_id)
> gives me all the correct items so far, but when I want to join another
> table I'm stuck.
>
> How can I join the other 2 tables? The error-message I get is
> Unknown column 'db.u.cat_id' in 'on clause' when I join the
> 'categories'-table.

Can anyone simplify the SQL-query:

SELECT DISTINCT u.name, a.date, a.title, c1.category, COUNT(c2.com_id)
AS comments
FROM users u
RIGHT JOIN articles a USING (user_id)
LEFT JOIN categories c1 USING (cat_id)
INNER JOIN articles a1 USING (cat_id)
LEFT JOIN comments c2 USING (art_id)
GROUP BY a.art_id

It gives me the right result, but maybe it can be done in a more 'clean'
way.

On Fri, 06 May 2005 16:01:09 +0200, Bert Melis wrote:

> Dear,
>
> I've got 4 tables:
>
> articles, users, categories and comments. Each table has a primary key:
> art_id, user_id, cat_id and com_id.
>
> The relations are simple:
> 1 article is written by 1 user and has 1 category. An article can have
> many comments.
>
> I want a query that gives me the article's subject, date, summary, the
> username and the number of comments.
>
>
> SELECT a.art_id, a.title, a.date, a.summary, u.name
> FROM articles a
> LEFT JOIN users u USING (user_id)
> gives me all the correct items so far, but when I want to join another
> table I'm stuck.
>
> How can I join the other 2 tables? The error-message I get is
> Unknown column 'db.u.cat_id' in 'on clause' when I join the
> 'categories'-table.

I will warn you that I really haven't tried to understand your database
structure in detail, but I do this kind of thing (joining 4 or more
tables) all the time, and as long as the relationships are not such that I
am looking for the NULL results in a field rather than the matches, the
query is rather straight forward.

I don't know what your foreign keys are which link the tables, but let us
suppose that an article has comments, categories have articles, and
users have articles. In this case, the database might be designed such
that articles contains an FK to users with user_id (here, assumes only
one author per article), comments has an FK to articles with art_id,
and articles has an FK to categories with cat_id.

If you wanted to select something from each table such that you really
truely needed to join all four tables, then you would try:

select *
from articles a, users u, categories c1, comments c2
where a.user_id = u.user_id and a.cat_id = c1.cat_id and c2.art_id =
a.art_id {... plus other where qualifiers to narrow down the search }

If you on the other hand want a query for the articles subject, date,
summary, username and number of comments, then

select a.art_id, a.title, a.date, a.summary, u.name,
count(*) as commentcount
from articles a, users u, comments c2
where a.user_id = u.user_id and c2.art_id =
a.art_id group by a.art_id

Here, you don't need to join the categories table because it is not needed
in the query (according to my design, which may differ from yours). Here,
you get the result by equi-joining all needed tables, and for each
equi-join you need a this = that in the where clause. If you did this
query without the group by clause, you would get a row for each article id
each time there was a comment for that article. What you say you want is
the count of these comments, but not the comments themselves. So you add
the group by clause, and request in the select clause to select the
count(*) which I am aliasing to the name commentcount. Using an aggregate
function like count(*) with a group by will aggregate only per group, not
the entire table.

Hope this helps.

Bert Melis wrote:
>
> I've got 4 tables:
>
> articles, users, categories and comments. Each table has a primary
key:
> art_id, user_id, cat_id and com_id.
>
> The relations are simple:
> 1 article is written by 1 user and has 1 category. An article can
have
> many comments.

OK, but you are forgetting to mention whether you have secondary
keys.  For example, the `articles` table should have a field that
corresponds to the ID of the user who wrote it; the `comments`
table should have a field that corresponds to the ID of the
article in response to which the comment is made.

> I want a query that gives me the article's subject, date, summary,
> the username and the number of comments.

Assuming you have the necessary fields in place in all tables,
it should look something like this:

SELECT a.art_id AS id,
a.title AS title,
a.date AS date,
a.summary AS summary,
u.name AS author,
COUNT(c.com_id) AS number_of_comments
FROM
(articles AS a LEFT JOIN users AS u ON (a.user_id = u.user_id))
LEFT JOIN comments AS c ON (a.art_id = c.art_id)
GROUP BY (comments.art_id);

Cheers,
NC