SQL - Create view from multiple tables

Question

I have three tables   POP country  year  pop  FOOD country  year  food  INCOME country  year  income    I am trying to create a view such as   V country  year  pop  food  income    This is my code so far  I don t think its correct   CREATE VIEW V AS  SELECT   FROM POP UNION SELECT   FROM FOOD UNION SELECT   FROM INCOME     One issue is  a country that is present in POP may not be present in FOOD  Same goes for year  A year that is present in POP may not be present in FOOD  So  I keep thinking that the above code will not work

User · Answer

Are you using MySQL or PostgreSQL   You want to use JOIN syntax  not UNION   For example  using INNER JOIN   CREATE VIEW V AS SELECT POP country  POP year  POP pop  FOOD food  INCOME income FROM POP INNER JOIN FOOD ON  POP country FOOD country  AND  POP year FOOD year  INNER JOIN INCOME ON  POP country INCOME country  AND  POP year INCOME year    However  this will only show results when each country and year are present in all three tables   If this is not what you want  look into left outer joins  using the same link above

User · Answer

Thanks for the help  This is what I ended up doing in order to make it work    CREATE VIEW V AS     SELECT       FROM   POP NATURAL FULL OUTER JOIN FOOD      NATURAL FULL OUTER JOIN INCOME

User · Answer

Union is not what you want  You want to use joins to create single rows  It s a little unclear what constitutes a unique row in your tables and how they really relate to each other and it s also unclear if one table will have rows for every country in every year  But I think this will work   CREATE VIEW V AS      SELECT i country i year p pop f food i income FROM     INCOME i   LEFT JOIN      POP p    ON     i country p country   LEFT JOIN     Food f   ON      i country f country   WHERE      i year p year   AND     i year f year      The left  outer  join will return rows from the first table even if there are no matches in the second  I ve written this assuming you would have a row for every country for every year in the income table  If you don t things get a bit hairy as MySQL does not have built in support for FULL OUTER JOINs last I checked  There are ways to simulate it  and they would involve unions  This article goes into some depth on the subject  http   www xaprb com blog 2006 05 26 how-to-write-full-outer-join-in-mysql

User · Answer

This works too and you dont have to use join or anything   DROP VIEW IF EXISTS yourview   CREATE VIEW yourview AS     SELECT table1 column1       table2 column2 FROM  table1  table2  WHERE table1 column1   table2 column1

[mysql] SQL - Create view from multiple tables

Examples related to mysql

Examples related to sql

Examples related to postgresql