CODEWITHSUNDEEP
mysqlsqldatabasemysql-workbench
user4954035
user4954035

Reputation:

Selecting from two tables and counting a third

I have a users, questions and answers table, What I wish to do is select from questions and users table based on their which is their username and then count the number of rows in the answers table based on the relationship between the questions and answers table. Bear in mind the current state of the tables are :

Questions table has four columns (question_id, topic_id, username, question) Answers table has two columns (question_id, answer) Users table has two columns (username, user_mail) The query i tried 

SELECT 
questions.question_id,
questions.username,
questions.question,
userlog.user_mail,
COUNT(answers.answer) as answerCount
FROM
questions
LEFT JOIN answers ON answers.question_id = questions.question_id, 
userlog
WHERE
questions.topic_id = '0d3fb89c012b5af12e1e0'
    AND userlog.username = questions.username

The problem with the above is that, it return only one row instead of three rows which are in the database.

Upvotes: 1

Views: 77

Answers (3)

Drew
Drew

Reputation: 24960

create table users
(
    userId int auto_increment primary key,
    username varchar(100) not null,
    email varchar (100) not null
);

create table questions
(
    qId int auto_increment primary key,
    topicId varchar(50) not null,
    userId int not null,
    question varchar(1000) not null
);

create table answers
(   
    aId int auto_increment primary key,
    qId int not null,
    answer varchar(1000) not null
);

insert users (username,email) values ('sparky','[email protected]'),('sarah','[email protected]');

truncate table questions;   -- for debugging
insert questions (topicId,userId,question) values ('0d3fb89c012b5af12e1e0',1,'Does life exist outside our galaxy?');
insert questions (topicId,userId,question) values ('0d3fb89c012b5af12e1e0',1,'Are fish really that dumb? Really?');
insert questions (topicId,userId,question) values ('xxxxxx',1,'Am I here?');
insert questions (topicId,userId,question) values ('xxxxxx',1,'Am you here?');

truncate table answers; -- for debugging
insert answers (qId,answer) values (1,'I hope so.');
insert answers (qId,answer) values (1,'I think so.');
insert answers (qId,answer) values (2,'What is wrong with you.');
insert answers (qId,answer) values (2,'Fish are nice.');
insert answers (qId,answer) values (2,'I like turtles.');
insert answers (qId,answer) values (3,'I like turtles too.');
insert answers (qId,answer) values (3,'Me 3.');

-- select * from users;
-- select * from questions;
-- select * from answers;


select 
q.qId,u.username,q.question,u.email,count(a.aId) as AnswerCount
from users u
join questions q
on q.userId=u.userId  and q.topicId='0d3fb89c012b5af12e1e0'
join answers a
on a.qId=q.qId
group by q.qId,u.username,q.question,u.email


+-----+----------+-------------------------------------+-----------+-------------+
| qId | username | question                            | email     | AnswerCount |
+-----+----------+-------------------------------------+-----------+-------------+
|   1 | sparky   | Does life exist outside our galaxy? | [email protected] |           2 |
|   2 | sparky   | Are fish really that dumb? Really?  | [email protected] |           3 |
+-----+----------+-------------------------------------+-----------+-------------+
2 rows in set (0.04 sec)

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82504

Try this:

SELECT questions.question_id,  
               questions.username, 
               questions.question, 
               userlog.user_mail, 
               (Select COUNT(answers.answer) where answers.question_id = questions.question_id) as answerCount 
FROM questions 
INNER JOIN userlog ON userlog.username = questions.username
WHERE questions.topic_id = '0d3fb89c012b5af12e1e0'

Upvotes: 1

O. Jones
O. Jones

Reputation: 108776

There are a few issues with your query. First, the presence of COUNT() makes the query into an aggregate query. Without GROUP BY, aggregate queries can't generate more than a single row.

Another issue: you've got some JOIN confusion with USERLOG. Hopefully there's only one row in USERLOG for each user, or you may end up double-counting answers.

Try this query 

SELECT questions.question_id,
       questions.username,
       questions.question,
       userlog.user_mail,
       COUNT(answers.answer) as answerCount
  FROM questions
  LEFT JOIN userlog ON questions.username = userlog.username
  LEFT JOIN answers ON answers.question_id = questions.question_id
 WHERE questions.topic_id = '0d3fb89c012b5af12e1e0'
 GROUP BY questions.question_id, questions.username, questions.question, userlog.user_mail
 ORDER BY questions.username, questions.question_id

That should yield the multirow result set you need.

Upvotes: 2

Related Questions