Hi there guys, I'm having a problem with a mySQL query. the dbs are as follows:
answer_value(id, answer_id)
user_answers(user_id, answer_value_id, answer_text)
I need to join the tables on answer_value.id = user_answers.answer_value_id and need to pull out every answer_value.id for a specific user_answers.user_id and need to be able to recognise where no answer_text occurs.
(the user_answers.answer_value_id's will be a subset of the answer_value.id's).
So far I have:
SELECT answer_value.id, user_answers.answer_value_id, user_answers.answer_text FROM answer_value LEFT JOIN (SELECT * FROM user_answers WHERE user_id = $userid) AS uav ON answer_value.id = user_answers.answer_value_id
But, of course it errors.
Thanks in advance for the help. It would be grately appreciated and I'm so tired...
Can anyone tell me how to do a FULL OUTER JOIN in mySQL
Moderator: General Moderators
Can anyone tell me how to do a FULL OUTER JOIN in mySQL
Last edited by doctorsrp on Wed Mar 10, 2004 5:16 pm, edited 1 time in total.
umm ... that would be subselects ... which are only available in mysql 5, try this,
Code: Select all
SELECT av.id, uav.answer_value_id, uav.answer_text
FROM answer_value AS av
LEFT JOIN user_answers AS uav
ON av.id=uav.answer_value_id
WHERE uav.user_id = $useridNot Solved Yet!!!!
Thanks for the help guys, not quite what I wanted. I want to have a list of every answer_value.id and the corresponding user_answers.answer_value_id either set if there is a match or blank if there isn't one.
I think I might need a FULL OUTER JOIN - can anyone help
Thanks
I think I might need a FULL OUTER JOIN - can anyone help
Thanks
Last edited by doctorsrp on Wed Mar 10, 2004 5:19 pm, edited 1 time in total.
Full outer join required
I think that I need to do a full outer join - can anyone tell me how to do that in MYSQL please.
UPDATE. I currently have:
SELECT av.id, uav.answer_value_id, uav.answer_text FROM answer_value AS av LEFT OUTER JOIN user_answers AS uav ON av.id = uav.answer_value_id) UNION (SELECT av.id, uav.answer_value_id, uav.answer_text FROM answer_value AS av RIGHT OUTER JOIN user_answers AS uav ON av.id = uav.answer_value_id WHERE av.id IS NULL
in order to attempt a full outer join but need to add a WHERE uav.user_id = clause and i'm not sure where to add it. Please advise.
Thanks
UPDATE. I currently have:
SELECT av.id, uav.answer_value_id, uav.answer_text FROM answer_value AS av LEFT OUTER JOIN user_answers AS uav ON av.id = uav.answer_value_id) UNION (SELECT av.id, uav.answer_value_id, uav.answer_text FROM answer_value AS av RIGHT OUTER JOIN user_answers AS uav ON av.id = uav.answer_value_id WHERE av.id IS NULL
in order to attempt a full outer join but need to add a WHERE uav.user_id = clause and i'm not sure where to add it. Please advise.
Thanks
Last edited by doctorsrp on Wed Mar 10, 2004 6:57 pm, edited 1 time in total.
No working
Thanks for the effort man but it isn't what I want. Please check my most recent post above!