Left Join and another join aswell???

errorCode30

Hi all,

Im having a problem with a database query. :queasy:

I have 3 tables:

users:

ID	Name
1	Bob
2	Jack
3	Paul
4	Mick


Message:

ID	UserID	MessageText
1	1	Hello
2	1	Hello Again
3	2	Im Jack
4	4	Im Mic


Replies:

ID	UserID	MessageID	ReplyText
1	3	1		Hello
2	1	1		Hi!
3	4	1		This is good
4	2	2		When
5	4	2		Take that!

And the result i would like to get is:

(For example, the result for looking at messageID 1)

Message: Hello username: Bob replies: 3

Getting the username from the "users" table, with a left join (i think), but then also counting the "MessageID" in the replies table to get the total replies for that message id.

Is it possible to do all this with one query?

Thanks for all the help!

Cheers 🙂

djjjozsi

its really necessary to store these messages in an another table ?
in the massages table take the user_from and replay_to_id
and if its value is 0 then that message is not a replay, but handle as a new message.

i've tried to imagine the three table version and it's query 🙂 Maybe today i Could not able to sleep 🙂

Piranha

I think that something like this would work. But I'm not sure, it is always hard when you group by using multiple joins.

select columns, count(m.id)
from users u
left join messages m on u.id = m.userid
left join replies r on r.messageid = m.id
group by m.id

djjjozsi

Hi!
Forget the 3 table.

2 is enoght:

users_

ID
Name

Message
ID
UserID <--- this who writes to iserid_to
userid_to <---

MessageText
replay_to <--- if it is a replay, then set the Message.ID value it. If its 0, then it is not a replay.

if the user has a userid: 1

the sql is:
SELECT users.Name, COUNT(Message.MessageText) AS replays ,Message.userid_to,Message.replay_to FROM Message INNER JOIN users ON Message.UserId=users.ID
GROUP BY
users.Name
HAVING (Message.userid_to=1 AND Message_.replay_to>0)

errorCode30;10891749 wrote:
Hi all,

Im having a problem with a database query. :queasy:

I have 3 tables:
users:

ID	Name
1	Bob
2	Jack
3	Paul
4	Mick


Message:

ID	UserID	MessageText
1	1	Hello
2	1	Hello Again
3	2	Im Jack
4	4	Im Mic


Replies:

ID	UserID	MessageID	ReplyText
1	3	1		Hello
2	1	1		Hi!
3	4	1		This is good
4	2	2		When
5	4	2		Take that!
And the result i would like to get is:

(For example, the result for looking at messageID 1)

Message: Hello username: Bob replies: 3

Getting the username from the "users" table, with a left join (i think), but then also counting the "MessageID" in the replies table to get the total replies for that message id.

Is it possible to do all this with one query?

Thanks for all the help!

Cheers 🙂

errorCode30

Thanks for the replies guys!!

I think i would like to go with djjjozsi's way of just using 2 tables -- it seems a good idea to cut down on the sql queries.

However, what i am trying to do is more like a forum, then emails, so there would not a field like "userid_to", so i'm kind of lost 🙁

I have cut down my tables to the following:

users:

ID	Name
1	Bob
2	Jack
3	Paul
4	Mick


Message:

ID	TopicID	UserID	MessageText	isReply
1	1	1	Hello		0
2	1	1	Hello Again	1
3	1	2	Im Jack		1
4	1	4	Im Mic		1
5	1	3	Im New		0
6	1	1	Another Msg	5
7	1	2	And Again	5
8	2	2	New Area	0
9	2	4	Great		8

I have been able to do the following:

SELECT users.ID, users.Name, Message.ID, Message.TopicID, Message.UserID, Message.MessageText, Message.isReply
FROM Message
LEFT JOIN users ON Message.UserID = users.ID
WHERE  Message.TopicID = '1' AND Message.isReply = '0'

This query gives me both messages in topic "1", but i do not know how to add in the "Count" to show the "isReply"

The query right now gives me:

users.ID	users.Name	Message.ID	Message.TopicID	Message.UserID	Message.MessageText	Message.isReply
1		Bob		1		1		1		Hello			0
3		Paul		5		1		3		Im New			0

What i would like is an extra column on the end that shows this:

ReplyCount:
3
2

So i can have the following:

Looking at TopicID = 1:

Message: Hello User: Bob Replies: 3

Message: Im New User: Paul Replies: 2

Looking at TopicID = 2:

Message: New Area User: Jack Replies: 1

Thanks again djjjozsi and Piranha 🙂

If anyone could help me add the "count" into my query, that would be amazing!

Regards,