使用LEFT JOIN复制记录

Question

At the below I have 2 tables. in first table I have a list of Models. on Second table I have added keys for some models. For example, i have added 2 keys (123 AND 125) for model with Id 1

models

id(AI)     model
1          100
2          101
3          100
4          105
5          107

modelKeys

keyid(AI)    model_id     key
   1           1          123
   2           1          125
   3           4          125
   4           5          127

Now, using PHP PDO I want to list all models and model's keys.

<?php

$query = 
"
SELECT
  m.*,
  mk.*
FROM
  models as m
LEFT JOIN modelKeys as mk
 ON m.id = mk.model_id
ORDER BY
 m.id
";

$sql = $db -> query($query);
$sql -> execute();

$fetch = $sql -> fetchAll(PDO::FETCH_ASSOC);

?>
<ol>

<?php

foreach($fetch as $models)
{
  echo "
   <li>
    $models[model]";

  foreach($fetch as $keys)
  {
    echo "<ul>";

    if($models['id'] == $keys['keyid'])
    {
      echo "<li>$keys[key]</li>";
    }

    echo "</ul>";
  }
  </li>";
}

?>

</ol>

RESULT

model

100
      123
      125
100
      123
      125
101
100
105
      125
107
      127

As you see, model with number 100 is duplicated, cause it has 2 keys. I tried to use SELECT DISTINCT and GROUP BY but did not get correct result.

CORRECT RESULTS SHOULD BE

model

100
      123
      125
101
100
105
      125
107
      127

Answer 1

I'm just curious. This is the result of your tables in a Fiddle i did: http://sqlfiddle.com/#!2/cae823/3

The result is

ID  MODEL   KEYID   MODEL_ID    KEYNR
1   100     1       1           123
1   100     2       1           125
2   101     (null)  (null)      (null)
3   100     (null)  (null)      (null)
4   105     3       4           125
5   107     4       5           127

As far as i can see your SQL doesn't have duplicates. (not if you used "m.id = mk.model_id" in your SQL). So this is almost exactly what you wanted (only the formatting needs adjusting).

You could loop through these results and with every change in MODEL do a <li> for MODEL. And then just print all the KEYNR's.

So in PHP something like this: (i didn't test this code for typo's):

<?php

$query = 
"
SELECT
  m.*,
  mk.*
FROM
  models as m
LEFT JOIN modelKeys as mk
 ON m.id = mk.model_id
ORDER BY
 m.id
";

$sql = $db -> query($query);
$sql -> execute();

$fetch = $sql -> fetchAll(PDO::FETCH_ASSOC);

echo "<ol>";

$lastmodelid = 0;
foreach($fetch as $row) {

  if ($row['id'] != $lastmodelid) {
    if ($lastmodelid!=0) { echo "</li>"; }
    echo "<li>$row['model']";
  }
  $lastmodelid = $row['id'];

  echo "<ul>";
  echo "<li>$row['keynr']</li>";
  echo "</ul>";

}
if ($lastmodelid!=0) { echo "</li>"; }
echo "<ol>";

?>

---

I'm assuming you don't create duplicates yourself with the MODEL_ID/KEYNR's. If you do then you do need to use some DISTINCT but in that case i would advice against doing a select on m.* and mk.* but just use the fields you want.

Like this: http://sqlfiddle.com/#!2/cae823/6

SELECT
  DISTINCT
  m.MODEL,
  mk.KEYNR
FROM
  models as m
LEFT JOIN modelKeys as mk
 ON m.id = mk.model_id
ORDER BY
 m.id

Result:

MODEL   KEYNR
100     123
100     125
101     (null)
100     (null)
105     125
107     127