How to get entities A from the database that have common entities B in another table?

Question

MySQL

Hello.
There are three tables. One - Orders with orders, the other - Products with products, the third - OrdersProducts - contains the id of the order and the id of the product in the order. Accordingly, you need to get orders from the Orders table that have common products among themselves (that is, all rows where orders1.order_id = orders2.order_id).
I solved it in a rather ugly and, as my intuition tells me, inefficient way.

poke

SELECT 
    products_by_orders1.order_number as n1, 
    products_by_orders2.order_number as n2, 
    COUNT(*) as count 

FROM 
    (
        SELECT DISTINCT orders.order_number, orders_products.product_id FROM products
        INNER JOIN orders_products 
        ON orders_products.product_id = products.id
        INNER JOIN orders
        ON orders_products.order_id = orders.id
    ) 
as products_by_orders1

INNER JOIN 
    (
        SELECT DISTINCT orders.order_number, orders_products.product_id FROM products
        INNER JOIN orders_products 
        ON orders_products.product_id = products.id
        INNER JOIN orders
        ON orders_products.order_id = orders.id
    ) 
as products_by_orders2 

ON products_by_orders1.product_id = products_by_orders2.product_id
WHERE products_by_orders1.order_number <> products_by_orders2.order_number
GROUP BY products_by_orders1.order_number, products_by_orders2.order_number

ORDER BY count DESC -- отсортируем по количеству общих продуктов в заказе

I could not find a more elegant solution, but I suspect that it simply has to be - I would like to get rid of at least two identical subqueries. Actually, the question is more of academic interest: how?
Thanks in advance.

Answer 1

select o1.*, o2.*
from order_products p1
join order_products p2 on p1.product_id = p2.product_id
    and p1.order_id != p2.product._id
join orders o1 on o1.id = p1.order_id
join orders o2 on o2.id = p2.order_id

Sorry for the syntax highlighting - I'm on the phone)
UPD formatted