I'm trying to create a view from different tables. One table contains attributes such as year, name, and country the second one contains population, country and the third country, size.
The problem with my code is that if the year & the name is the same for one country I get outputs like:
Year Name population size
1900, Luffy, 32000, 500000
1900, Luffy, 31200, 41000
1900, Luffy, 31200, 341000
Because the year and the name are the same the output is wrong. If the year would have been different it would have been ok even if the same name is repeated. I tried to use the Distinct but it did not seem to work. And when I tried to group by year the program crashed. Is there any way of solving this that the year must be distinct IF the name is the same and if the name is the same only one of them should be shown based on some conditions?
enter code here
CREATE VIEW viewTest AS
SELECT DISTINCT t1.year, t1.name, t2.population, t3.size
FROM table1 t1, table2 t2, table3 t3;