Left join specifics.

[Ninjakreborn]

										$sql = "SELECT DISTINCT LIST_NO, Date_Time FROM rets_oh_oh
										LIMIT " . $offset . ", " . $limit;
										$query = mysql_query($sql);
										echo mysql_error();
										while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
											echo '<pre>';
											print_r($row);
											echo '</pre>';
										}

 

I have 3 different tables with various property information.  The listings in the code above, return the MLS number.  The main thing is, that MLS number can be located in one of three different tables.

Table1, Table2, or Table3.  I am thinking that I was setting up a left join, below is what I have...

 

										$properties = array(); // This will contain the properties, no matter how we get them.
										$sql = "SELECT * FROM rets_oh_oh
										LEFT JOIN rets_resi_mf ON rets_resi_mf.LIST_NO = rets_oh_oh.LIST_NO
										LEFT JOIN rets_resi_sf ON rets_resi_sf.LIST_NO = rets_oh_oh.LIST_NO
										LEFT JOIN rets_resi_cc ON rets_resi_cc.LIST_NO = rets_oh_oh.LIST_NO
										LIMIT " . $offset . ", " . $limit;
										$query = mysql_query($sql);
										while ($property_row = mysql_fetch_array($query, MYSQL_ASSOC)) {
											if ($property_row['STATE'] == 'MA' || $property_row['STATE'] == 'Massachusetts') {
												if (!in_array($property_row['LIST_NO'], $properties)) {
													$properties[$property_row['LIST_NO']] = $property_row;
												}
											}
										}
										*/

 

But there are a lot of issues. I only want to get unique list numbers, I only want to get a certain date range, I need to only get ones from a certain state and other things. I as in the process of doing a basic query, and then trying to use a PHP Array to strip them out...but since there is pagination, there are duplicates on alternate pages (page 2, and 3 and 4).  Is there a way I can roll this into 1 query without it messing up. 

[Ninjakreborn]

Just to help out the readers out there, I got a good bit of it worked out.

 

										$todays_date = date("Y-m-d H:i:s");
										$future_date = date("Y-m-d H:i:s", strtotime('+7 days'));
										$sql = "SELECT DISTINCT rets_oh_oh.LIST_NO, rets_oh_oh.Date_Time,
										rets_resi_sf.STATE as STATE, rets_resi_mf.STATE as STATE, rets_resi_cc.STATE as STATE,
										rets_resi_sf.STREET_NO as STREET_NO, rets_resi_mf.STREET_NO as STREET_NO, rets_resi_cc.STREET_NO as STREET_NO,
										rets_resi_sf.STREET_NAME as STREET_NAME, rets_resi_mf.STREET_NAME as STREET_NAME, rets_resi_cc.STREET_NAME as STREET_NAME,
										rets_resi_sf.TOWN, rets_resi_mf.TOWN, rets_resi_cc.TOWN,
										rets_resi_sf.REMARKS, rets_resi_mf.REMARKS, rets_resi_cc.REMARKS
										FROM rets_oh_oh
										LEFT JOIN rets_resi_sf ON rets_resi_sf.LIST_NO = rets_oh_oh.LIST_NO AND (rets_resi_sf.STATE = 'MA' OR rets_resi_sf.STATE = 'Massachusetts')
										LEFT JOIN rets_resi_mf ON rets_resi_mf.LIST_NO = rets_oh_oh.LIST_NO AND (rets_resi_mf.STATE = 'MA' OR rets_resi_mf.STATE = 'Massachusetts')
										LEFT JOIN rets_resi_cc ON rets_resi_cc.LIST_NO = rets_oh_oh.LIST_NO AND (rets_resi_cc.STATE = 'MA' OR rets_resi_cc.STATE = 'Massachusetts')
										WHERE rets_oh_oh.Date_Time >= '" .  $todays_date . "' AND rets_oh_oh.Date_Time >= '" .  $future_date . "'
										GROUP BY rets_oh_oh.LIST_NO
										LIMIT " . $offset . ", " . $limit;
										$query = mysql_query($sql);
										echo mysql_error();
										while ($row = mysql_fetch_array($query, MYSQL_ASSOC)) {
											echo '<pre>';
											print_r($row);
											echo '</pre>';
										}

 

All I need to do now is filter states that are empty, and setup a few other filters, then I am good. Will post my final code when I am done to help anyone in the future who is messing around with convoluted joins.

[fenway]

FYI, you can't mix GROUP BY and those columns that way.