creating a sql view-CodePudding

how can i create a sql view that collects " data that only exists in all 8 table" ? for example i want to retrieve/ create a view that shows lists of countries that exists in all tables.

CodePudding user response：

You need to better define your question, it is very generic, to create a view you simply prepend this code to the statement you want to turn into a view

CREATE VIEW vwMYViewName
AS

For the second part of your question, with this you can get only the country names that are in the 8 tables

CREATE VIEW vwCOUNTRYSONALLTABLES
AS
SELECT Country
FROM ( SELECT Country FROM Tbl1
        UNION ALL
        SELECT Country FROM Tbl2
        UNION ALL
        SELECT Country FROM Tbl3
        UNION ALL
        SELECT Country FROM Tbl4
        UNION ALL
        SELECT Country FROM Tbl5
        UNION ALL
        SELECT Country FROM Tbl6
        UNION ALL
        SELECT Country FROM Tbl7
        UNION ALL
        SELECT Country FROM Tbl8
    ) DtAllCountrys
GROUP BY Country
HAVING COUNT(Country) = 8

CodePudding user response：

Without seeing your data set it's hard to know exactly what to do.

So you have 8 tables.... And you want a list of countries.

You could do something like this. Note I would consider union all if you want to look for counts etc, but if you are just looking for a distinct list of countries you could do this and union would make your list distinct between each table.

CREATE VIEW vwCOUNTRYLIST
AS
SELECT Country from Table1
UNION
SELECT Country from Table2
UNION
SELECT Country from Table3
UNION
SELECT Country from Table4
UNION
SELECT Country from Table5
UNION
SELECT Country from Table6
UNION
SELECT Country from Table7
UNION
SELECT Country from Table8