MySQL Join two Tables, give all entries of table 1

Question

I have three tables.

Table 1: tbl_user

id	username
1	User A
2	User B

Table 2: tbl_location

id	location
1	City 1
2	City 2
3	City 3

Table 3: tbl_userlocation

fk_user	fl_location
1	1
2	1
2	2

Now I have a HTML-Page to edit user data

I have two querys. One for reading the userdata and one for the locations.

public function readOneUser($id)
{
    $stmt = $this->pdo->prepare("SELECT * FROM tbl_user WHERE id = :id");
    $stmt->execute([
        'id' => $id
    ]);
    $stmt->setFetchMode(PDO::FETCH_CLASS, "administration\\CMR\\UserModel");
    $res = $stmt->fetch(PDO::FETCH_CLASS);
    return $res;
}

That's fine. All is good. I have the normal userdata like username, mail, phone...

Now I want a complete list of all locations, BUT (and that's the problem) all locations where the user is assigned should be checked. Locations where the user is not assigned should not be checked but listed.

<?php
    foreach ($readLocations AS $location):
    ?>
    <input type="checkbox" name="location" <?= htmlspecialchars($location->id == $location->fk_location) ? "checked" : "" ?> value="<?= $location->id; ?>"><span><?= $location->location; ?></span><br>
    <?php
    endforeach;
?>

I have this query. I know it's wrong, but that's the "best" I have. Hope you can help me to make it correct.

public function readAllLocationsForEdit($id)
{
    $stmt = $this->pdo->prepare("
        SELECT tbl_location.*, tbl_userlocation.*
        FROM tbl_location
        LEFT JOIN tbl_userlocation ON tbl_userlocation.fk_location = tbl_location.id 
        WHERE fk_user = :id
        GROUP BY location");
    $stmt->execute([
        'id' => $id
    ]);
    $res = $stmt->fetchAll(PDO::FETCH_CLASS, "administration\\CMR\\LocationModel");
    return $res;
}

With this I get all locations where a user is assigned, but not the locations where the user is not assigned. I know, that the WHERE-clause is not correct, but all other I tested give me not the result I want.

Ah! Gotcha. I had a hard time following the logic. Will delete. — JNevill, Apr 12 '22 at 20:25

score 3 · Accepted Answer · answered Apr 12 '22 at 20:22

3

The WHERE clause is applied after the JOIN.

For your needs, you need to filter the userlocation map either before or during the join. Either include the user filter in the join predicate, or join on a sub-query that filters the user (the former being the usual, and cleaner, approach).

     FROM tbl_location
LEFT JOIN tbl_userlocation
       ON tbl_userlocation.fk_location = tbl_location.id 
      AND tbl_userlocation.fk_user     = :id

Or...

     FROM tbl_location
LEFT JOIN (SELECT * FROM tbl_userlocation WHERE fk_user = :id) AS tbl_userlocation 
       ON tbl_userlocation.fk_location = tbl_location.id

answered Apr 12 '22 at 20:22

MatBailie

83,401
18
103
137

Big thanks for the solution. Btw: You edit my post to show the tables correct. Also thanks for this. Could you please explain me, what I did wrong? So the next time I could make it better. The preview seemed to be OK. – TheQuestionmark Apr 12 '22 at 20:30
@TheQuestionmark the markup language only recognises the table syntax if there is a blank line before it. If you look at the edit history you'll see I just added three blank lines. – MatBailie Apr 12 '22 at 20:31

MySQL Join two Tables, give all entries of table 1

1 Answers1