Joining 3 MySQL tables to display all data

Question

I've read countless threads on stackoverflow regarding matters similar to this, but I'm struggling to solve my issue and to understand the various MySQL JOIN queries. I'm in the early days of learning MySQL, and any help would be appreciated.

I have 3 tables:

1. auction
2. category
3. lot

auction and category simply have a unique primary key and an auction date, category title column respectively.

lot displays the lot title, lot description etc... and links to the auction and category table using the their respective ID's.

I simply wish to display the lot table showing all rows including the auction date and category title.

My failed attempt:

SELECT l.lotid, l.lotnumber, l.lottitle, l.lotdescription, c.categorytitle, a.auctiondate, l.estimatefrom, l.estimateto, l.photo, l.datecreated, l.lastmodified 
FROM lot l
JOIN category c ON l.categoryid = c.categoryid
JOIN auction a ON l.auctionid = a.auctionid
ORDER BY l.lotnumber;

This is simply acting like a WHERE query, as I'm sure it should, omitting any rows whereby categorytitle and auctiondate do not match

Is there a simple fix?

Many thanks

Answer 1

In the interest of my time and patience, I simply coded this the long way, throwing in a subquery to get the result I wanted:

<?php
$query = "SELECT l.lotid, l.lotnumber, l.lottitle, l.lotdescription, c.categorytitle, l.estimatefrom, l.estimateto, l.photo, l.datecreated, l.lastmodified 
         FROM lot l
         JOIN category c ON l.categoryid = c.categoryid
         ORDER BY l.lotnumber";

if (!$mysqli->query($query)) {
    echo '<p class="warning">Error executing SELECT query: (' . $mysqli->errno . ') ' . $mysqli->error . "</p>";
}

if ($result = $mysqli->query($query)) {

  while ($row = $result->fetch_row()) {

?>
    <div class="clearfix lot-bg">       
        <p>Lot Number: <span><?php echo $row[1]; ?></span></p>
        <p>Lot Title: <span><?php echo $row[2]; ?></span></p>
        <p>Lot Description: <span><?php echo $row[3]; ?></span></p>
        <p>Category: <span><?php echo $row[4]; ?></span></p>

        <?php

        $subquery = "SELECT a. auctiondate 
                     FROM lot l
                     JOIN auction a ON l.auctionid = a.auctionid
                     WHERE lotid = $row[0]";

        if ($subresult = $mysqli->query($subquery)) {

            if ($stmt = $mysqli->prepare($subquery)) {

            $stmt->execute();
            $stmt->store_result();

            $subrows = $stmt->num_rows;

               for ($j = 0; $j < $subrows; ++$j) {
                    while ($subrow = $subresult->fetch_row()) {
                        echo '<p>Auction Date: <span>' . $subrow[0] . '</span></p>';
                    }
                }
            }
            $stmt->close();
        }
        $subresult->close();

        ?>

        <p>Estimate:  <span>£<?php echo $row[5]; ?> - £<?php echo $row[6]; ?></span></p>
        <p>Date Created: <span><?php echo $row[8]; ?></span></p>
        <p>Last-Modified:  <span><?php echo $row[9]; ?></span></p>
        <figure><a class="fancybox" rel="group" href='img/<?php echo $row[7]; ?>'><img src='img/<?php echo $row[7]; ?>'></a></figure>

        <div class="buttons">           
            <form class="form-signin form-delete" action="lot.php" method="post">
                <input type="hidden" name="delete" value="yes" />
                <input type="hidden" name="lotid" value='<?php echo $row[0]; ?>' />
                <button type="submit" class="btn btn-small btn-primary btn-block">DELETE</button>
            </form>
            <form class="form-signin form-delete" action="lotupdate.php" method="post">
                <input type="hidden" name="edit" value="yes" />
                <input type="hidden" name="lotid" value='<?php echo $row[0]; ?>' />
                <button type="submit" class="btn btn-small btn-primary btn-block">EDIT</button>
            </form>
            <form class="form-signin form-delete" action="lotupdate.php" method="post">
                <input type="hidden" name="photo" value="yes" />
                <input type="hidden" name="lotid" value='<?php echo $row[0]; ?>' />
                <button type="submit" class="btn btn-small btn-primary btn-block">PHOTO</button>
            </form>
        </div>
    </div>

<?php

    }
    $result->close();
}
$mysqli->close();
?>

Answer 2

You can simply use the left join or right to reduce the number of rows and will get the appropriate data.

Here is tutorials: LEFT JOIN & RIGHT JOIN.

Answer 3

Use LEFT JOIN:

SELECT 
    l.lotid, 
    l.lotnumber, 
    l.lottitle, 
    l.lotdescription, 
    IFNULL(c.categorytitle,'n/a'), 
    IFNULL(a.auctiondate,'n/a'), 
    l.estimatefrom, 
    l.estimateto, 
    l.photo, 
    l.datecreated, 
    l.lastmodified 
FROM 
    lot l
LEFT JOIN category c 
    ON l.categoryid = c.categoryid
LEFT JOIN auction a 
    ON l.auctionid = a.auctionid
ORDER BY 
    l.lotnumber;