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

qotsa42

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

View on DB Fiddle

Barmar

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

How to query a pivot table data in MySQL (many to many relationship)

From Dev

MySQL, join many to many relationship query

From Dev

many to many query mysql return duplicate rows

From Dev

Many to many relationship and MySQL

From Dev

Mysql Many to Many relationship

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

Remove duplicate rows on many to many table (Mysql)

From Dev

MySQL 1064 Error Creating a Linking Table (Many-to-Many)

From Dev

Is there a way to simplify a Linq query with a many to one relationship?

From Dev

Is there a way to simplify a Linq query with a many to one relationship?

From Dev

Best way to query a Many to Many Relationship using pg-promise

From Dev

Laravel Many to Many Relationship - Linking 3 items

From Dev

hibernate not locating junction table in query of many to many relationship

From Dev

how to query many-to-many relationship in same table by redbeanphp?

From Dev

Query for retrieving data from a bridging table of many to many relationship

From Dev

hibernate not locating junction table in query of many to many relationship

From Dev

Query many to many relationship with DetachedCriteria

From Dev

how to query a many to many relationship?

From Dev

eloquent: query many to many relationship

From Dev

Best way to implement many-to-many relationship in MySQL

From Dev

How to query for many to many relationship between products and filters in MySQL?

From Dev

php mysql many to many relationship query result display

From Dev

create mysql table with one to many relationship

From Dev

Recursive relationship on a many to many table

From Dev

SQL many to many relationship table

From Dev

Many to many relationship in the same table?

From Dev

Junction table/many to many relationship

From Dev

MySQL - How to insert into table that has many-to-many relationship

From Dev

MySQL many-to-many table relationship with three entities

Related Related

  1. 1

    How to query a pivot table data in MySQL (many to many relationship)

  2. 2

    MySQL, join many to many relationship query

  3. 3

    many to many query mysql return duplicate rows

  4. 4

    Many to many relationship and MySQL

  5. 5

    Mysql Many to Many relationship

  6. 6

    Remove duplicate rows on many to many table (Mysql)

  7. 7

    Remove duplicate rows on many to many table (Mysql)

  8. 8

    MySQL 1064 Error Creating a Linking Table (Many-to-Many)

  9. 9

    Is there a way to simplify a Linq query with a many to one relationship?

  10. 10

    Is there a way to simplify a Linq query with a many to one relationship?

  11. 11

    Best way to query a Many to Many Relationship using pg-promise

  12. 12

    Laravel Many to Many Relationship - Linking 3 items

  13. 13

    hibernate not locating junction table in query of many to many relationship

  14. 14

    how to query many-to-many relationship in same table by redbeanphp?

  15. 15

    Query for retrieving data from a bridging table of many to many relationship

  16. 16

    hibernate not locating junction table in query of many to many relationship

  17. 17

    Query many to many relationship with DetachedCriteria

  18. 18

    how to query a many to many relationship?

  19. 19

    eloquent: query many to many relationship

  20. 20

    Best way to implement many-to-many relationship in MySQL

  21. 21

    How to query for many to many relationship between products and filters in MySQL?

  22. 22

    php mysql many to many relationship query result display

  23. 23

    create mysql table with one to many relationship

  24. 24

    Recursive relationship on a many to many table

  25. 25

    SQL many to many relationship table

  26. 26

    Many to many relationship in the same table?

  27. 27

    Junction table/many to many relationship

  28. 28

    MySQL - How to insert into table that has many-to-many relationship

  29. 29

    MySQL many-to-many table relationship with three entities

HotTag

Archive