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.

  • Full joining 2 views is no different than full joining 2 tables. Shows us what you tried. – ypercubeᵀᴹ May 14 '15 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 '15 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 '15 at 20:12
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, privacy policy and cookie policy

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