A complex NATURAL JOIN - COUNT query !

Anon

Hi all,

I have a complex problem, complex for me! It's for a intern development.Users can send the same message in different forum (ROOM), the messages can be commented, but each comment goes in only one forum... even if the commented has been send in several forums.

I summarize you my base (3 tables blamed in the question):

T_MESSAGES_MGS (Table for messages)

MGS_ID

TJ_MGSROO (Table to link messages and forum)

MGSROO_ID -> ID for replies.
MGS_ID -> ID of messages.
ROO_ID -> ID of forums.

T_REPLY_RPY (Table for replies)

RPY_ID
MGSROO_ID

The query (which work) that I currently use does not allow me to know the number of replies of a message in a forum (ROO):
SELECT MGS.MGS_ID, MGS.MGS_SUBJECT, etc...
NATURAL JOIN TJ_MGSROO
NATURAL JOIN T_MESSAGES_MGS
WHERE ROO_ID = 1 ORDER BY MGS.MGS_DATE_LASTMODIFIED DESC

OK, when a user enters a forum, the user sees the messages of the forum thanks to the ID of forum ROO_ID.
What I'd like, is to know directly in this query the number of replies calculating the number of MGSROO_ID in T_REPLY_RPY:
SELECT MGS.MGS_SUBJECT, MGS.USR_LOGIN, COUNT(T_REPLY_RPY.MGSROO_ID), MGS.MGS_DATE FROM T_MESSAGES_MGS MGS
NATURAL JOIN TJ_MGSROO
NATURAL JOIN T_MESSAGES_MGS
NATURAL JOIN T_REPLY_RPY
WHERE ROO_ID = 1 ORDER BY MGS.MGS_DATE_LASTMODIFIED DESC

But each one of my tries were concluded by error messages, or false results

Any idea????
Thank you, Vincent.

Anon

Do the count as a scalar subquery.

SELECT MGS.MGS_SUBJECT, MGS.USR_LOGIN, (select COUNT(MGSROO_ID) from T_REPLY_RPY natural join TJ_MGSROO
where ROO_ID = 1)
, MGS.MGS_DATE FROM T_MESSAGES_MGS MGS
NATURAL JOIN TJ_MGSROO
NATURAL JOIN T_MESSAGES_MGS
NATURAL JOIN T_REPLY_RPY
WHERE ROO_ID = 1 ORDER BY MGS.MGS_DATE_LASTMODIFIED DESC

I've may have messed on the join/where in sub-select.

Which DBMS are you using? Their implementation of natural join is not ANSI compliant.

Anon

Unfortunately, I can't use this query 'cos I am using MySQL.
This could work with PostregSQL, but not with MySQL I use.

Thanks,
Vincent.

Anon

As usual with Mysql. You have to do the count in a separate query.