Mysql - Is there a way to query a many-to-many relationship and include rows not in the linking table?

Question

I am designing an inventory management app for a company with multiple locations. Each location shares the same base set of products, but the number of products that are in-stock at each location differs by store.

I have created a basic wireframe of what the database would look like here:

https://www.db-fiddle.com/f/49paZZocLknGr23Woabp7Q/1

Here are the tables and some sample data:

CREATE TABLE locations (
    id int primary key NOT NULL AUTO_INCREMENT,
    name varchar(64)
);

CREATE TABLE products (
    id int primary key NOT NULL AUTO_INCREMENT,
    name varchar(64)
);

CREATE TABLE locations_products (
    id int primary key NOT NULL AUTO_INCREMENT,
    location_id int,
    product_id int
);

INSERT INTO locations (name) VALUES
    ('phoenix'), ('denver'), ('houston'), ('dallas'), ('miami');
    
INSERT INTO products (name) VALUES
    ('nicotine'), 
    ('valium'), 
    ('vicodin'),
    ('marijuana'), 
    ('ecstasy'), 
    ('alcohol'), 
    ('cocaine'), 
    ('covid');
    
INSERT INTO locations_products (location_id, product_id) VALUES
    (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
    (2, 2), (2, 3), (2, 4), (2, 5),
    (3, 1), (3, 2), (3, 3), (3, 4), (3, 5), (3, 6), (3, 7), (3, 8),
    (4, 6), (4, 7), (4, 8),
    (5, 1), (5, 2), (5, 3), (5, 4);

The locations_products table stores the many-to-many relationship between locations and products. If a product is in stock at a specific location, a row with the location_id and the product_id go into locations_products.

It's simple to query for the products in-stock at a location:

SELECT l.name, t.name
FROM tests t
INNER JOIN locations_tests lt
    ON t.id = lt.test_id
INNER JOIN locations l
    ON lt.location_id = l.id
where lt.location_id = 3;

However, it's more difficult to get the products that are NOT in stock, and the query becomes somewhat complex to get a list of ALL products and whether they're in stock:

SELECT t.name, l.name as location, 'yes' as in_stock
FROM  products t
LEFT JOIN locations_products lp
    ON t.id = lp.product_id
LEFT JOIN locations l
    ON lp.location_id = l.id
   WHERE l.id = 1

UNION 

SELECT distinct p.name, 'phoenix' as location, 'no' as in_stock
from products p
LEFT JOIN locations_products lp
    ON p.id = lp.product_id
LEFT JOIN locations l
    ON lp.product_id = l.id
where p.id not in (
    select lp.product_id from locations_products lp where lp.location_id = 1
)

This query produces the desired results, but it has issues. It's super ugly to hard-code the location name into the SELECT statement and the DISTINCT query shoots up red flags.

Can anyone suggest a better way to get the same result set for this location but without hardcoding the SELECT statement in the second query of the UNION statement? I am also open to suggestions for better approaches to this problem. Thanks!

name	location	in_stock
nicotine	phoenix	yes
valium	phoenix	yes
vicodin	phoenix	yes
marijuana	phoenix	yes
ecstasy	phoenix	yes
alcohol	phoenix	no
cocaine	phoenix	no
covid	phoenix	no

Answer 1

Use an IF() expression to test whether the linked column was found.

To get the location names in all rows, add a cross join with locations that isn't dependent on whether the product is in stock there.

SELECT t.name, l1.name as location, IF(l.id IS NULL, 'no', 'yes') as in_stock
FROM  products t
CROSS JOIN locations AS l1
LEFT JOIN locations_products lp
    ON t.id = lp.product_id AND lp.location_id = l1.id
LEFT JOIN locations l
    ON lp.location_id = l.id
WHERE l1.id = 1

Also move the condition on the joined table into the ON clause, so it doesn't filter out the non-matching rows from the results.

DEMO