CODEWITHSUNDEEP
mysql
Hanna Teobald
Hanna Teobald

Reputation: 44

SQL Select multiple even if empty

My table structures

Table 1
bed {
- bed_id
- patient_bed_id
- station
}
patient_bed_id == patient_id from patient table
bed_id is just a unique number

Entries
-------------------------
1       1       1
2       0       1
3       0       1
4       0       1
5       4       1
6       0       2
7       0       2
8       2       2
9       0       2
10      3       2
-------------------------

Table 2
patient {
- patient_id
- patient_name
}

Entries
-------------------------
1       Test Name1
2       Test Name2
3       Test Name3
4       Test Name4
5       Test Name5
-------------------------

So what I try is this

https://prnt.sc/1sr6r1h

If the bed is free it should be green and the name should be "-", but if there is an patient added to it, it should be red and the other 11 beds should be still green. But as you can see on the screenshot, if i'm going to try this im getting just one result. I tried it with this sql

SELECT * FROM bed,patient WHERE station = 'Station 1' AND patient_bed_id = patient_id LIMIT 12

This gives me just one single result and here I got stuck now. How do I get still all bed entries even there is no patient on it?

My PHP code is this currently, but with a good SQL code, I could get everything even smarter and shorter.

<?php
$sql ="SELECT * FROM bed,patient";
$query = $pdo -> prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);                                   
if($query->rowCount() > 0){
    $i = 0;
    foreach($results as $result){
        if($result->patient_bed_id == $result->patient_id && $result->station == 'Station 1'){
            echo '
                <div class="col-6 col-md-4 col-xl-2">
                    <a class="block block-rounded text-center" href="bed.php?id='.$result->patient_bed_id.'">
                        <div class="block-content">
                            <p class="mt-5 mb-10">';
                                if($result->patient_bed_id == ''){
                                    echo '
                                    <i class="fa fa-bed text-success fa-2x d-xl-none"></i>
                                    <i class="fa fa-bed text-success fa-3x d-none d-xl-inline-block"></i>';
                                }else{
                                    echo '
                                    <i class="fa fa-bed text-danger fa-2x d-xl-none"></i>
                                    <i class="fa fa-bed text-danger fa-3x d-none d-xl-inline-block"></i>';
                                }
                                echo '</p>';
                                if($result->patient_bed_id != ''){
                                    echo '<p class="font-w600 font-size-sm text-uppercase">'.$result->patient_name.'</p>';
                                }
                        echo '</div>
                    </a>
                </div>
            ';
        }elseif($result->patient_bed_id != $result->patient_id && $result->station == 'Station 1'){
            echo '
                <div class="col-6 col-md-4 col-xl-2">
                    <a class="block block-rounded text-center" href="bed.php?id='.$result->patient_bed_id.'">
                        <div class="block-content">
                            <p class="mt-5 mb-10">';
                                if($result->patient_bed_id == ''){
                                    echo '
                                    <i class="fa fa-bed text-success fa-2x d-xl-none"></i>
                                    <i class="fa fa-bed text-success fa-3x d-none d-xl-inline-block"></i>';
                                }else{
                                    echo '
                                    <i class="fa fa-bed text-danger fa-2x d-xl-none"></i>
                                    <i class="fa fa-bed text-danger fa-3x d-none d-xl-inline-block"></i>';
                                }
                                echo '</p>';
                                if($result->patient_bed_id == ''){
                                    echo '<p class="font-w600 font-size-sm text-uppercase">-</p>';
                                }
                        echo '</div>
                    </a>
                </div>
            ';
        }
    if(++$i > 11) break;
    }
}
?>

Now you can see my problem and hopefully someone can explain a better SQL. Thanks for any help.

Upvotes: 0

Views: 718

Answers (1)

O. Jones
O. Jones

Reputation: 108651

Your SQL needs a LEFT JOIN clause in place of your comma-join clause.

SELECT *
  FROM bed
  LEFT JOIN patient 
             ON patient_bed_id = patient_id 
 WHERE station = 'Station 1'
 LIMIT 12

An ordinary join like yours suppresses any rows from bed that have no corresponding patient rows. The LEFT JOIN keeps those unmatched rows from bed and returns null values for the patient. That makes real-world sense: a bed with a null patient is an empty bed.

I hope your hospital has at least a few empty beds in this horrible time of pandemic.

Upvotes: 1

Related Questions