mysql - i want to retrieve all data from the above 4 tables, based on property id -

mysql - i want to retrieve all data from the above 4 tables, based on property id -

basically site property agent,

i want retrieve columns below 4 tables, based on property id.

short descrip: wp_tm_properties -> add together main property name(id,property_name)

wp_tm_sub_properties -> add together sub-property based on main property

wp_tm_tenants --> add together tenants name , other related info based on property & property

wp_tm_reports --> add together tenants study based on property , sub property

create table if not exists `wp_tm_properties` ( `id` int(11) not null auto_increment, `property_name` varchar(255) default null, primary key (`id`) ) engine=myisam default charset=utf8 auto_increment=3 ; create table if not exists `wp_tm_sub_properties` ( `id` int(11) not null auto_increment, `prop_id` int(11) not null, `sub_property_name` varchar(255) default null, primary key (`id`) ) engine=myisam default charset=utf8 auto_increment=5 ; create table if not exists `wp_tm_tenants` ( `id` int(11) not null auto_increment, `prop_id` int(11) not null, `sub_property_id` int(11) not null, `ten_name` varchar(255) default null, `ten_phone` varchar(255) default null, `ten_email` varchar(255) default null, `ten_address` varchar(255) default null, `ten_sdate` date not null, `ten_edate` date not null, `contract_form` varchar(255) not null, primary key (`id`) ) engine=myisam default charset=utf8 auto_increment=5 ; create table if not exists `wp_tm_reports` ( `id` int(11) not null auto_increment, `prop_id` int(11) not null, `sub_property_id` int(11) not null, `ten_name` varchar(255) not null, `rep_sdate` date not null, `rep_des` varchar(255) not null, `rep_action` varchar(155) not null, `rep_edate` int(11) not null, `rep_status` varchar(255) not null, primary key (`id`) ) engine=myisam default charset=utf8 auto_increment=4

use joins connect tables on related columns:

select * `wp_tm_properties` p bring together `wp_tm_sub_properties` sp on p.`id` = sp.`prop_id` bring together `wp_tm_tenants` t on t.`prop_id` = p.`id` , t.`sub_property_id` = sp.`id` bring together `wp_tm_reports` r on r.`prop_id` = p.`id` , r.`sub_property_id` = sp.`id`

this usesinner joinand homecoming rows there matching info in tables; if want homecoming rows properties without tenants should useleft joininstead.

also, columnten_nameseems duplicated between tenants , reports; maybe latter table should have columntenant_idinstead?

mysql sql