MySQL Queries, using multiple table fields with the same name in a where clause

Question

I have a select statement with multiple joins, each one of them has a column name called 'created_on' is there a way to make it so that the where portion of the query will check all 3 tables? Here is the actual query I made

SELECT *
FROM 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
WHERE catchment_area.household_id IN (
  SELECT household_address.household_id
  FROM 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
  WHERE DATE(created_on) BETWEEN '2014-03-01' AND '2014-03-31' 
)

The joins I am talking about are the joins in the subquery.

Answer 1

Try using alias' with additional WHERE clauses.

SELECT 
    *
FROM 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
WHERE catchment_area.household_id IN (
  SELECT household_address.household_id
  FROM 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
  WHERE DATE(ha.created_on) BETWEEN '2014-03-01' AND '2014-03-31' 
  AND DATE(ca.created_on) BETWEEN '2014-03-01' AND '2014-03-31' 
  AND DATE(pc.created_on) BETWEEN '2014-03-01' AND '2014-03-31' 
)

I don't actually know which three tables have the 'created_on' field so I went with household_address, catchment_area and previous_cases as an example. Change these to the ones you actually want to search.

(also should probably alias all of the table names)