msyql union of left and right join plus where conidtion not working

Lets say I have two tables in MySql.

value (Address Field Values)

field (Address Fields)

It might be some people (user_id in the value table) have not filled out all addressFields. which means that they have only values for some fields.

To retrieve all their values with all other fields that they have not used yet, I am doing a Union of Left and Right join: (A union is used as MYSQL does not have a FULL OUTER JOIN).

SELECT field.*, value.*
FROM field
LEFT JOIN value ON field.id = value.field_id 
WHERE 
value.user_id='4'

union

SELECT field.*, value.*
FROM field  
RIGHT OUTER JOIN value ON field.id = value.field_id
WHERE 
value.user_id='4'

The above union is not doing what I want to achieve. User=4 has only 4 of 50 fields filled in. The result of the query is only returning the 4. If I replace in the second select the

value.user_id='4'

with a

GROUP BY field.id

then it is returning 54 rows (4 filled in plus all 50 possible fields with values from other users). I have also tried to use dummy values for value.* but then also the union is not merging the records correctly, it is again returning 54 rows.

is there are way to achieve what I need? Thanks for any ideas….

Solution

I think a simple LEFT JOIN will work:

SELECT fields.*, value.*
  FROM field

  LEFT JOIN value
    ON field.id = value.field_id
   AND value.user_id = 4

The problem occures when you filter the final result for user_id=4 because not all fields are filled for this user. Therefore in the final result all fields which are not filled properly will have null for column user_id and are removed by the where condition.