Can anyone tell me how to do a FULL OUTER JOIN in mySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
doctorsrp
Forum Newbie
Posts: 10
Joined: Fri Feb 20, 2004 12:08 am

Can anyone tell me how to do a FULL OUTER JOIN in mySQL

Post by doctorsrp »

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...
Last edited by doctorsrp on Wed Mar 10, 2004 5:16 pm, edited 1 time in total.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

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 = $userid
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

subselects are available from 4.1.x
doctorsrp
Forum Newbie
Posts: 10
Joined: Fri Feb 20, 2004 12:08 am

Not Solved Yet!!!!

Post by doctorsrp »

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
Last edited by doctorsrp on Wed Mar 10, 2004 5:19 pm, edited 1 time in total.
doctorsrp
Forum Newbie
Posts: 10
Joined: Fri Feb 20, 2004 12:08 am

Full outer join required

Post by doctorsrp »

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
Last edited by doctorsrp on Wed Mar 10, 2004 6:57 pm, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

SELECT *
FROM table1
LEFT OUTER JOIN table2 ON table1.id=table2.id
doctorsrp
Forum Newbie
Posts: 10
Joined: Fri Feb 20, 2004 12:08 am

No working

Post by doctorsrp »

Thanks for the effort man but it isn't what I want. Please check my most recent post above!
Post Reply