Access query problem! Please help!

[samoi]

Hi,

I have three tables

users -|--------<| topics -|--------<| replies

 

users's PK is user_id

topics PK is topic_id

replies PK is reply_id

 

What i want is to select user_id # 1. And then count all topics posted by him/her. Also, count all replies post by him/her!

 

I am sick of trying. It didn't work!

this is the syntax!

SELECT users.user_type, count(topics.topic_creator) as Tc, count(reply.reply_by) as Rc FROM users, topics, reply WHERE  users.user_id = 1 AND topics.topic_creator = 1 AND reply.reply_by = 1
GROUP BY users.user_id

 

The real topics posted by user_id #1 is : 3 topics

# of replies by that user is : 2

 

But it's displaying as result . topics = 6 , replies = 6!

 

Any help is much much appreciated!

[andrewgauger]

I think this is it.

SELECT users.user_type,
count(topics.topic_creator) as Tc, 
count(reply.reply_by) as Rc 
FROM users INNER JOIN topics ON
(topics.topic_creator = users.user_id)
INNER JOIN reply ON
(reply.reply_by = users.user_id)
WHERE users.user_id = 1

[samoi]

I think this is it.

SELECT users.user_type,
count(topics.topic_creator) as Tc, 
count(reply.reply_by) as Rc 
FROM users INNER JOIN topics ON
(topics.topic_creator = users.user_id)
INNER JOIN reply ON
(reply.reply_by = users.user_id)
WHERE users.user_id = 1

 

Thank you my friend for your help.

It doesn't really work!

It keep saying "Syntax error (Missing operator) in query expression "(topics.topic_creator = users.user_id)

INNER JOIN reply ON

(reply.reply_by = users.user_id)"

 

This is the problem I was facing

 

 

any tries ?

 

Thank you my friend once again

[andrewgauger]

Yeah, I don't know if this works in one query like that.  I think you might need to do a subquery:

 

SELECT user_type, (SELECT count(*) FROM topics WHERE topic_creator=1), (SELECT count(*) FROM reply WHERE reply_by=1) FROM users WHERE user_id=1

 

But that is experimental (never done this before)

[samoi]

Yeah, I don't know if this works in one query like that.  I think you might need to do a subquery:

 

SELECT user_type, (SELECT count(*) FROM topics WHERE topic_creator=1), (SELECT count(*) FROM reply WHERE reply_by=1) FROM users WHERE user_id=1

 

But that is experimental (never done this before)

 

Yeah that worked my friend

I really appreciate it man

 

I'm getting to love this wonderful forums people!

 

Thank you, Thank you!