1

I have all kinds of views in mysql with employee data. I have tried to use methods like this -https://stackoverflow.com/questions/2384298/why-does-mysql-report-a-syntax-error-on-full-outer-join but they simply don't work for views.

I end up getting more of a LEFT JOIN as a result. Is there a way to merge two views without including duplicates in mysql - I am currently using a 4 view process to duplicate this.

Improve this question
3
  • Full joining 2 views is no different than full joining 2 tables. Shows us what you tried.
    – ypercubeᵀᴹ
    May 14, 2015 at 19:54
  • @ypercube - I did exactly what was in the link. I got all of the entries that weren't matches. I have tried unions and group bys but I have to run them over several views to get them to work. I can get it working but not in one view statement. I want to know how I can take viewA and add viewB (and ViewC and ViewD...) where there are no duplicate ids.
    – LOSTinDB
    May 14, 2015 at 20:08
  • Did you try the answer there with 3 views/tables? I think that answer is wrong when it comes to 3+ tables. Edit the question and add the SHOW CREATE VIEW viewname; output.
    – ypercubeᵀᴹ
    May 14, 2015 at 20:12

1 Answer 1

Reset to default
1

Simulation of FULL OUTER JOIN:

( SELECT ... FROM a LEFT JOIN b ON ... ) -- Intersection, plus rest of b
UNION ALL  -- There will be no overlap; ALL is faster
( SELECT ... FROM b LEFT JOIN a ON ... WHERE a.id IS NULL ) -- Just rest of a

(The link given is less efficient because it gets the Intersection twice, then dedups by doing UNION DISTINCT.)

If you need a FULL OUTER JOIN between more than 2 tables, then it gets messier. (But you did not ask for that.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.