MySQL inner joins?

Question

I have a database design for a survey.

I have a Table USERS with user_id PK and name.

QUESTION with question_id and question_text.

POSSIBLE_ANSWER with answer_id PK ,value, text.

Also, I have USER_ANSWERS with user_id from users , Question_id , answer_id .

Also some 3 other tables not relevant to my question.

What I need is select avg(value) for all users where name like'm%'.

Hope this is clear thanks.

Answer 1

You could do this:

SELECT u.name, AVG(pa.value) AS avgvalue
FROM USER_ANSWERS ua
INNER JOIN USERS u ON u.user_id = ua.user_id
INNER JOIN POSSIBLE_ANSWER pa ON pa.answer_id = ua.answer_id
WHERE u.name LIKE 'm%'
GROUP BY u.name

Though I'm still interested in reading what you have tried so far. And you could add u.user_id to your select list and group by that instead (like PatrikAkerstrand did) - which you'd have to do if your names aren't unique

Answer 2

SELECT u.user_id, u.name, AVG(pa.value) 
FROM USER_ANSWERS ua 
INNER JOIN POSSIBLE_ANSWER pa ON pa.answer_id = ua.answer_id
INNER JOIN USERS u ON ua.user_id = u.user_id
WHERE u.name LIKE 'm%'
GROUP BY u.user_id

Remove GROUP BY if you want the average of all users with name starting with 'm'. This query gives the average PER user, whose name starts with m.

Answer 3

try the following:

select avg(PA.value)
from POSSIBLE_ANSWER PA
inner join USER_ANSWERS UA on PA.answer_id = UA.answer_id
inner join USERS U on U.user_id = UA.user_id 
where U.name like 'm%'