[Resolved] Left Join?

[Nate]

SELECT b1.dmarkScore AS 3DMark03,
             b2.AquaMarkScore AS AquaMark,
             b3.SystemTestMarks AS PCMark04,
             b4.PassMarkRating AS Performance
FROM bench3DMark03 b1, benchAqua b2, benchPCMark04 b3, benchPerf b4
WHERE b1.dmarkID = '17'
AND b2.aquaID = '65'
AND b3.pcmarkID = '71'
AND b4.perfID = '29'

This query works except if 1 of the 4 ID's are null or can't be found in its table, the query fails. Is there a way to return the result with null fields (if they cant be found in their tables)? Would this be a LEFT or RIGHT JOIN or something? If so can you provide an example?

[robert_w_ark]

Originally posted by Nate

SELECT b1.dmarkScore AS 3DMark03,
             b2.AquaMarkScore AS AquaMark,
             b3.SystemTestMarks AS PCMark04,
             b4.PassMarkRating AS Performance
FROM bench3DMark03 b1, benchAqua b2, benchPCMark04 b3, benchPerf b4
WHERE b1.dmarkID = '17'
AND b2.aquaID = '65'
AND b3.pcmarkID = '71'
AND b4.perfID = '29'

This query works except if 1 of the 4 ID's are null or can't be found in its table, the query fails. Is there a way to return the result with null fields (if they cant be found in their tables)? Would this be a LEFT or RIGHT JOIN or something? If so can you provide an example? [/B]

Not sure if this will entirely work, but maybe you will get the point..

<?php

$check1 = mysql_query("SELECT b1.dmarkScore AS 3DMark03, b2.AquaMarkScore AS AquaMark, b3.SystemTestMarks AS PCMark04, b4.PassMarkRating AS Performance WHERE b1.dmarkID = '17'");
$check2 = mysql_query("SELECT b1.dmarkScore AS 3DMark03, b2.AquaMarkScore AS AquaMark, b3.SystemTestMarks AS PCMark04, b4.PassMarkRating AS Performance WHERE b2.aquaID = '65'");
$check4 = mysql_query("SELECT b1.dmarkScore AS 3DMark03, b2.AquaMarkScore AS AquaMark, b3.SystemTestMarks AS PCMark04, b4.PassMarkRating AS Performance WHERE b3.pcmarkID = '71'");
$check5 = mysql_query("SELECT b1.dmarkScore AS 3DMark03, b2.AquaMarkScore AS AquaMark, b3.SystemTestMarks AS PCMark04, b4.PassMarkRating AS Performance WHERE b4.perfID = '29'");
if($check1)
{
  if($check2)
  {
    if($check3)
    {
      if($check4)
      {
        if($check5)
        {
          echo "All checks are valid.";
        }
        else
        {
          echo "Cannot pass check number 5.";
        }
      }
      else
      {
        echo "Cannot pass check number 4.";
      }
    }
    else
    {
      echo "Cannot pass check number 3.";
    }
  }
  else
  {
    echo "Cannot pass check number 2.";
  }
}
else
{
  echo "Cannot pass check number 1.";
}

?>

Have fun -Rob

[Roger_Ramjet]

It would be a LEFT JOIN that you need. Only I don't see which columns you will be joining on looking at you existing query.

[Nate]

Thanks guys, I figured it out with a little trial/error.

Appreciate the help!

$sql = "SELECT bench3DMark03.dmarkScore AS 3DMark03,
                         bench3DMark05.dmarkScore AS 3DMark05,
                         benchAqua.AquaMarkScore AS AquaMark,
                         benchPCMark04.SystemTestMarks AS PCMark04,
                         benchPerf.PassMarkRating AS Performance
             FROM benchmark b
             LEFT JOIN bench3DMark03 ON (b.bench3DMark03 = bench3DMark03.dmarkID)
             LEFT JOIN bench3DMark05 ON (b.bench3DMark05 = bench3DMark05.dmarkID)
             LEFT JOIN benchPCMark04 ON (b.benchPCMark04 = benchPCMark04.pcmarkID)
             LEFT JOIN benchAqua ON (b.benchAquamark = benchAqua.aquaID)
             LEFT JOIN benchPerf ON (b.benchPerformance = benchPerf.perfID)
             WHERE b.orderID = '2051'";