SQL Join two tables only if they both contain all records-CodePudding

I have samples, sample_products, products, orders and order_products.

Samples <=> Products (through sample_products)

Order <=> Products (through order_products).

There is no connection between Order and Samples.

Now I need to find the most used Samples on an Order. This is particularly a problem because if we for example have a package of 2 products - product A and product B, an order needs to contain both of them, not only product A or not only product B. An order has to contain only and exactly those 2 products to be considered a sample.

I tried joining them like this but this leads to the problem I mentioned before - it does not ensure the order does not contain other products or that both A and B are present.

SELECT * FROM samples 
INNER JOIN sample_products ON sample_products.sample_id = samples.id
INNER JOIN products ON products.id = sample_products.product_id
INNER JOIN order_products ON order_products.product_id = products.id
INNER JOIN orders ON orders.id = order_products.order_id

How can I make sure that it only joins the table together if both product A and B are present?

CodePudding user response：

If you want to count full sample set (if sample1 consits from A,B,C -> count oreder with A,B,C), you can use number of product for join:

SELECT order_sample_count.sample_id,
       COUNT(order_sample_count.order_id) as sample_use
FROM
(
  SELECT sample_id, count(product_id) as semple_numbers
  FROM 
  (
    SELECT DISTINCT sample_id, sample_products.product_id
    FROM samples
    INNER JOIN sample_products ON sample_products.sample_id = samples.id
  ) AS sample_distinct
  GROUP BY sample_id
) AS semple_count
INNER JOIN 
)
  SELECT order_id, sample_id, count(product_id) as semple_numbers
  FROM
  (
    SELECT DISTINCT order_products.order_id, sample_products.sample_id, order_products.product_id
    FROM order_products
    INNER JOIN sample_products ON sample_products.product_id = order_products.product_id
  ) AS order_sample_distinct
  GROUP BY order_id, sample_id
) AS order_sample_count ON semple_count.sample_id = order_sample_count.sample_id 
AND semple_count.semple_numbers = order_sample_count.semple_numbers
GROUP BY order_sample_count.sample_id