join

Question

my question is

suppose i have 5 tables.that are countrymaster,statemaster,citymaster,wardmaster,zonemaster

1.countrymaster-->pk.countryid,countryname
2.statemaster-->pk.stateid,f.k.countryid,statename
3.citymaster-->pk.cityid,f.k.stateid,cityname
4.wardmaster-->pk.wardid,fk.cityid,wardname
5.zonemaster-->pk.zoneid,fk.wardid,zonename

that type of structure in tables..

now i hav to create a view of those table.

i mean my view structure is countryname,statename,cityname,wardname,zonename..

so how can i do using join.

mohit ranjan · Answer

create view aaa as select * from zonemaster a inner join wardmaster b on b.wardid=a.wardid inner join citymaster c on c.cityid=b.cityid inner join statemaster d on d.stateid=c.stateid inner join statemaster e on e.countryid=d.countryid

Upamanyu Roy Choudhury · Answer

Hi Vikas The view is nothing but a logical collection of records from the table. There are 5 tables in your example and if you can either create views on each table or can create a view by joining on all of the 5 tables. A sample can be like the following CREATE VIEW your_view_vw AS SELECT * FROM country_master a JOIN state_master b ON b . c ountryid= a .countryid Like wise join on the basis of primary and foreign key and produce the final view with 5 tables. I hope this solves your problem please keep me posted and mark it as answer

Upamanyu Roy Choudhury · Answer

Hi Vikas The view is nothing but a logical collection of records from the table. There are 5 tables in your example and if you can either create views on each table or can create a view by joining on all of the 5 tables. A sample can be like the following CREATE VIEW your_view_vw AS SELECT * FROM country_master a JOIN state_master b ON b . c ountryid= a .countryid Like wise join on the basis of primary and foreign key and produce the final view with 5 tables. I hope this solves your problem please keep me posted and mark it as answer