Hi,
"You have an error in your SQL syntax near 'UNION SELECT * FROM dependentProfile WHERE CompanyID=2 AND COBRA='Yes' ORDER ' at line 3"

I cannot figure out the syntax error. What is wrong?

 
  $query2 ="SELECT * FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='Yes' ORDER BY EmployeeID
	UNION 
	SELECT * FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='Yes' ORDER BY EmployeeID";

$result2 = mysql_query($query2) or die("Error with query2:" . mysql_error() );	

    Hi Tracey,

    asuming you are using MySQL: UNION ist supported from Version >= 4.0
    Below that versoin UNION will cause an Syntax-Error as you described.

    Salomo

      Bummer.
      So how would I do a query that combines the 2?
      I've tried various combinations of AND/OR and I do not get the results I'm looking for
      The closest I've come is the following which gets the proper dependentProfiles, but no employeeProfiles (returns the dependent of the employee instead of the employee)

       $query2 = "SELECT * FROM employeeProfile,dependentProfile 
          WHERE (employeeProfile.CompanyID=$CoID OR dependentProfile.CompanyID=$CoID) 
          AND employeeProfile.COBRA='Yes' AND dependentProfile.COBRA='Yes' 
          OR 
          (employeeProfile.CompanyID=$CoID AND employeeProfile.COBRA='Yes')
           ORDER BY dependentProfile.EmployeeID"; 
      

      Here is the print statement:

      while($line = mysql_fetch_array($result2))
      {
      	$eeID      = $line["EmployeeID"];
      	$Elast	 = $line["EmployeeLast"];
      	$Efirst     = $line["EmployeeFirst"];	
      
      $dID      = $line["DependentID"];
      $Dtype     = $line["DependentType"];
      $Dlast	 = $line["DependentLast"];
      $Dfirst     = $line["DependentFirst"];	
      
      //?? how do I say 'if dependent/ or 'if employee' ?????
      // I use the approach that if $Dtype has a value...do this, else assume type is employee... and do that
      // is there a better approach ?????????????
      
      if ( ($Dtype=="Spouse") || ($Dtype=="Minor") || ($Dtype=="Other"))
       {  
      $tableinfo .= "<tr bgcolor=$color>
      <td nowrap>$Dtype: $dID</td>	
      <td nowrap>$Dlast, $Dfirst</td>
      </tr>\n";
      }
      
      //Otherwise, type is Employee 
      	else
      	{
      	$tableinfo .= "<tr>
      	<td nowrap>Employee: $eeID</td>	
      	<td nowrap>$Elast, $Efirst</td>
      	</tr>\n";
      	}
      }
      
      print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
             <tr>
      	  <th align=\"left\" valign=\"bottom\">Type & ID </th>
      	  <th align=\"left\" valign=\"bottom\">Name</th>
      
        </tr>";
      
      print "$tableinfo";
      print "</table>";
      print "<br>";
      

      Here is the table structure:

      dependentProfile

      DependentID (auto-increment)
      EmployeeID
      CompanyID
      DependentFirst
      DependentLast

      COBRA

      employeeProfile

      EmployeeID (auto-increment)
      CompanyID
      EmployeeFirst
      EmployeeLast
      COBRA

        I've gotten a bit closer in that I can get the employe profile and dependent profile to print in the same table, but I cannot figure out how to properly sort the results by $lastname.

        $query2 = "SELECT * FROM dependentProfile WHERE CompanyID=$CoID AND COBRA='Yes'
        	ORDER BY EmployeeID";
        $result2 = mysql_query($query2) or die("Query 2 error : " . mysql_error() ); 
        
        $query3 = "SELECT * FROM employeeProfile WHERE CompanyID=$CoID AND COBRA='Yes'
        	ORDER BY EmployeeID";
        $result3 = mysql_query($query3) or die("Query 3 error : " . mysql_error() );  
        
        while($line = mysql_fetch_array($result2))
        {
        	$dID      = $line["DependentID"];
        	$Dtype    = $line["DependentType"];
        	$Dlast     = $line["DependentLast"];
        	$Dfirst     = $line["DependentFirst"];	
        
        $tableinfo .= "<tr bgcolor=$color>
        <td nowrap>$Dtype: $dID</td>	
        <td nowrap>$Dlast, $Dfirst</td>
        </tr>\n";
        }
        
        
        while($line = mysql_fetch_array($result3))
        {
        	$eeID      = $line["EmployeeID"];
        	$Elast	 = $line["EmployeeLast"];
        	$Efirst     = $line["EmployeeFirst"];	
        
        $tableinfo .= "<tr>
        <td nowrap>Employee: $eeID</td>	
        <td nowrap>$Elast, $Efirst</td>
        </tr>\n";
        }
        
        print "<table cellpadding=1 cellspacing=1 width=100% border=\"0\" bordercolor=\"#cccccc\">
               <tr>
        	  <th align=\"left\" valign=\"bottom\">Type & ID </th>
        	  <th align=\"left\" valign=\"bottom\">Name</th>	
        	  </tr>";
        
        print "$tableinfo";
        print "</table>";
        print "<br>";
        
          Write a Reply...