Need a mysql case statement that checks a cell

I currently have 2 tables, lets say Table_A contains hundreds of sandwiches along with info about them and pictures, and Table_B contains specific sandwich groups. For example:

Table_A will have 4 columns:

Column 1 "id" which is auto-incrementing
Column 2 "sandwich_name" which contains Meatball sandwich 1, Meatball Sandwich 2, Meatball Sandwich 3, Turkey Sandwich 1, etc
Column 3 "sandwich type", in this case would be Meatball Sandwich, Meatball Sandwich, Meatball Sandwich, Turkey Sandwich
Column 4 "sandwich_picture" column which contains the link to the picture.

Table_B will help to categorize the sandwiches:

Column 1 called "sandwich_category" which corresponds with Column 3 from Table_A, contains the titles "Meatball Sandwich", "Turkey Sandwich"
Column 2 "featured_sandwich_id" where I can select an ID of a sandwich from Table_A which I have a SELECT statement which pulls its featured image:
```
SELECT featured_sandwich_id FROM Table_B WHERE sandwich_category='Meatball Sandwich' AND featured_sandwich_id != 0
```

I then have a if/while code which loads the featured sandwich image.

The issue I am having, is in some cases, there will be a new sandwich_type in Table_A which does not yet have its' own sandwich_category row in Table_B, in which case I want to have the code automatically pull image from the most recently inputted sandwich from Table_A. In other words, in the case that the featured_sandwich_id is not yet set in Table_B, I would like for the select statement to automatically pull the ID and image of the most recently posted sandwich from Table_A.

I'll try to be more specific and write out the procedure I would like the code to go through:

1 Auto-created div for each type of sandwich_category.
2: Div includes Table_B's sandwich_category name and checks if featured_sandwich_id has been set in Table_B.
3: If featured_sandwich_id has been set, pull the image associated with the sandwich id in Table_A.
4: If featured_sandwich_id has NOT been set (or if for some reason the sandwich_category has not yet been detailed in Table_B), use the sandwich_category name and image of the most recent sandwich from said sandwich_category, and populate them in the div above.

Here is my current code:

    $QUERY = "(
    SELECT Table_A.sandwich_picture, Table_A.sandwich_type, Table_B.featured_sandwich_id 
    FROM Table_A 
    LEFT JOIN Table_B 
    ON (Table_A.id = Table_B.featured_sandwich_id) 
    WHERE (Table_A.sandwich_type = 'Meatball Sandwich') 
    LIMIT 1)"

The above LEFT JOIN has an image for a 'Meatball Sandwich' and all other sandwich_types, but it is never the image for the associated featured_sandwich_id.

Switching the Statement above to a RIGHT JOIN always has the image for the featured_sandwich_id for 'Meatball Sandwich' and any other sandwich_type, but in the case that the featured_sandwich_id has not been set in the table, it pulls 0 results.

Here is the rest of the PHP as an example:

    ... while($query_results){

    echo '<div class="sandwich_category">
    <img src="$query_results['sandwich_picture']">
    <br>
    <p class="sandwich_category_name">';
    echo $query_results['sandwich_category']; // This is the category name
    echo '</p>
    </div>';

    }

As I understand it is best to condense into a single statement for the sake of saving resources, however I cant figure the correct type of join.

Any idea on how to accomplish this?

Solution

Try LEFT JOIN, ORDER BY and DESC:

SELECT b.sandwich_category, b.featured_sandwich_id, a.id, a.sandwich_name, a.sandwich_type, a.sandwich_picture
FROM Table_B b
LEFT JOIN Table_A a ON b.sandwich_category = a.sandwich_type
WHERE b.sandwich_category = 'Meatball Sandwich'
      AND b.featured_sandwich_id != 0 ORDER BY a.id DESC

What I just want to clarify is, you want to link those two tables by Table_A's sandwich_type to Table_B's sandwich_category, right? But the content of Table_B's sandwich_category is in string. So that means Table A's sandwich_type is a string?

Why not just link them by Table_B's primary id? It will be much faster and reliable.

UPDATE:

SELECT b.sandwich_category, b.featured_sandwich_id, a.id, a.sandwich_name, a.sandwich_type, a.sandwich_picture
FROM Table_B b
LEFT JOIN Table_A a ON b.featured_sandwich_id = a.id
                    AND b.sandwich_category = a.sandwich_type
WHERE b.sandwich_category = 'Meatball Sandwich' AND b.featured_sandwich_id != 0 ORDER BY a.id DESC