sql - MySQL Queries, using multiple table fields with the same name in a where clause -
i have select statement multiple joins, each 1 of them has column name called 'created_on' there way make portion of query check 3 tables? here actual query made
select * household_address join catchment_area on catchment_area.household_id = household_address.household_id join bhw on catchment_area.bhw_id = bhw.user_username join master_list on master_list.person_id = catchment_area.person_id join house_visits on house_visits.household_id = household_address.household_id catchment_area.household_id in ( select household_address.household_id demo.household_address join catchment_area on catchment_area.household_id = household_address.household_id join previous_cases on catchment_area.person_id = previous_cases.person_id join active_cases on catchment_area.person_id = active_cases.person_id join ls_report on ls_report.ls_household = household_address.household_name date(created_on) between '2014-03-01' , '2014-03-31' )
the joins talking joins in subquery.
try using alias' additional clauses.
select * household_address join catchment_area on catchment_area.household_id = household_address.household_id join bhw on catchment_area.bhw_id = bhw.user_username join master_list on master_list.person_id = catchment_area.person_id join house_visits on house_visits.household_id = household_address.household_id catchment_area.household_id in ( select household_address.household_id demo.household_address ha join catchment_area ca on ca.household_id = ha.household_id join previous_cases pc on ca.person_id = pc.person_id join active_cases ac on ca.person_id = ac.person_id join ls_report ls_r on ls_r.ls_household = ha.household_name date(ha.created_on) between '2014-03-01' , '2014-03-31' , date(ca.created_on) between '2014-03-01' , '2014-03-31' , date(pc.created_on) between '2014-03-01' , '2014-03-31' )
i don't know 3 tables have 'created_on' field went household_address, catchment_area , previous_cases example. change these ones want search.
(also should alias of table names)
Comments
Post a Comment