Using UNION for a single menu

Date: March 1st, 2008   No comments

Working on an in-house content management system, I came across a problem regarding my use of unions in mysql. The system has a file that is assigned to a particular job or jobs. With a html menu, i would like the user to be able to change the relationship to any other active job inline with the job it belongs to. The objective was to display the current record selection regardless of it's status along with all other records with the status of active. The mysql Union of two select statements works to combined my results sets.

If the file belongs to no jobs the trick was to provide ('') to the IN section of the first select statement. If it is empty our first part of the union will produce nothing but not get in the way.

$sql = "(
	SELECT s.status_name, g.group_id, j.job_id, CONCAT(UCASE(j.job_code), ' ',j.job_title) as title FROM jobs as j 
	LEFT JOIN groups as g using(group_id) 
	LEFT JOIN status as s using(status_id) 
	WHERE j.job_id IN (";
	
	$countofids = count($jobidarray);
	if($countofids >= 1){
		for ( $i = 0; $i < $countofids; $i++ ){
			if($i+1 == $countofids ){
				$sql .= "'" . $jobidarray[$i] . "'";
			} else {
				$sql .= "'". $jobidarray[$i] . "'" .',';
			}
		}
	} else {
		$sql .= "''";
	}

$sql .= ") 
	ORDER BY j.job_code
	)";
$sql .= "UNION
	(
	SELECT s.status_name, g.group_id, j.job_id, CONCAT(ucase(j.job_code), ' ',j.job_title) as title FROM jobs as j 
	LEFT JOIN groups as g using(group_id) 
	LEFT JOIN status as s using(status_id) 
	WHERE s.status_name = 'active' 
	AND j.active = 'Y' 
	AND g.group_id = '{$group_id}' 
	ORDER BY j.job_code 
	)";

MySQL reference