Unable to use Select Distinct with Join

[swatisonee]

Hi: I have the foll. code. The table "Reports" has multiple records for a given value of CID in the Field CID. I'd like to be able to select only 1 of them so that a list of customers appearing in the Reports table is available for selection in the dropdown alphabetically.  The foll. code does it but it doesnt list the Customers alphabetically. And when I use Join, the query doesnt run. I get a blank list .  The Field CID is common to both tables- Reports and Customers.

 

Could someone help me with the Join ? Thanks. Swat

<?php

$sqlco =  "SELECT DISTINCT CID FROM `Reports` ";

$resultco = mysql_query($sqlco) or die (mysql_error() ) ;

if ($myrowco = mysql_fetch_array($resultco) )
{

  do {
$cid = $myrowco["CID"];
$sqlrep = "SELECT * FROM `Customers` WHERE `CID` = '$cid' " ;
$resultrep = mysql_query($sqlrep) or die (mysql_error() ) ;
$myrowrep = mysql_fetch_array($resultrep);
$company = $myrowrep["Company"];

     printf("<option value=%d>
      %s , %s", $myrowco["CID"], $myrowrep["Company"], $myrowco["Mdate"]);

  } while ($myrowco = mysql_fetch_array($resultco));

}

else 
{

echo "No records found." ;	
}

?></select></a>

 

What i tried was this :

 

<?php

$sqlco =  "SELECT DISTINCT CID FROM `Reports` r JOIN  `Customers` c  WHERE r.CID = c.CID ORDER BY c.Company asc ";

$resultco = mysql_query($sqlco) or die (mysql_error() );

if ($myrowco = mysql_fetch_array($resultco) )
{

  do {

     printf("<option value=%d>
      %s ", $myrowco["CID"], $myrowco["Company"]);

  } while ($myrowco = mysql_fetch_array($resultco));

}

else 
{

echo "No records found." ;	
}

?>

[swatisonee]

Folks...anything you can spot on what might be going wrong with this code ? Much obliged. Thanks.

[jcbones]

Have you tried a scalar subquery?

 

SELECT * FROM Customers WHERE CID = (SELECT DISTINCT(CID) FROM Reports LIMIT 1) ORDER BY Company

[swatisonee]

Well this is what finally worked.

SELECT * FROM Customers c RIGHT OUTER JOIN Reports r USING (CID)  GROUP BY c.`Company`  asc

Thanks .

 

S