'How to get records that has relationship with all records in another table in many to many relationships in MySQL?

I am using Laravel 8 and MySQL8. I have these tables:

products
    id - integer
    name - string

factories
    id - integer
    name - string

factory_product
    factory_id - integer
    product_id - integer

As you can see the products table has a many-to-many relationship with the factories table. Now I want to get products that are produced by all factories. How to do that? I need SQL code.

mysqlsql


Solution 1:[1]

Try this query:

SELECT product_id
FROM factory_product
GROUP BY product_id
HAVING COUNT(factory_id) = (SELECT COUNT(*) FROM factories);

dbfiddle demo link

Factories Table:

id name
f1 fa
f2 fb
f3 fc
f4 fd
f5 fe

Product Table:

id name
p1 pa
p2 pb
p3 pc

Factory_Product Table:

factory_id product_id
f1 p1
f1 p2
f1 p3
f2 p1
f2 p3
f3 p1
f3 p3
f4 p1
f4 p3
f4 p2
f5 p1
f5 p2
f5 p3

The Output my Query Producing:

product_id
p1
p3

Solution 2:[2]

By use of belongsToMany relation

  • here this function add in your products model

    public function withFactories() { return $this->belongsToMany(factories::class, factory_product::class,'product_id', 'factory_id'); }

  • add function in your controller

    $all_product = products::with('withFactories)->get();

  • here must have foreign key in factory_product table

  • here you can get value of product value & Factories detail in withFactories array

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2

Related Questions