distinct view

[dmpe]

Hi!
(I am very novice in MySQL)
Is it possible create view(virtual table?) from distinct values of main tables ? Or I always must create two real tables with foreign key ?
Example :
I have main table:

ID| Name| City
1 | Ivan | Moscow
2 | Dmitry | Moscow
3 | John | New York

Can I have view from City column with automatic update values ?

Moscow
New York

And when I add next row to main table:
4| Mario |Rome

I want automaticaly update view :

Moscow
New York
Rome

 

[r937]

views are in mysql 5.0, which is still in alpha status

you do not need another table with a foreign key

just use

select distinct city from maintable

rudy

http://r937.com/

SQL Consulting

 

[dmpe]

I know about "select distinct". But it is VERY resource consumption action.
Of course, I may make "view functionality" by myself with two tables
and foreign key, but it's DB's duty I mean.

 

[r937]

a view for SELECT DISTINCT would be EXACTLY as resource intensive as the query

you may want to try creating an index on that column

rudy

http://r937.com/

SQL Consulting

 

[dmpe]

Why would be intensive ?
When I add row to maintable DB engine check if value in the "City" column
exist in the view. If exist - do nothing. If no - add value to the view.
But with deletion we must check existance any other row
with same "City" and delete city from view only when no row
with the same city in maintable exist. It would be resource intensive.

Index may help. I'll try.