Selections through SQL

Question

I am attempting to create a list of all orders that contain six specific products. The current query I'm using is:

    SELECT o.order_date,
    o.price AS revenue,
    o.cost,
    f.id AS fileid,
    o.id AS orderid,
    o.fk_order_status_id,
    o.date_started,
    o.date_completed,
    p.description AS product,
    pp.description AS subproduct

    FROM orders o

    JOIN file f 
        f.id = o.fk_file_id
    JOIN product p 
        ON p.id = o.fk_product_id
    JOIN product_product pp 
        ON pp.fk_product_id = o.fk_product_id
        
    WHERE o.fk_product_id IN ('66','8','6','21','11')

Which pulls all orders that have ANY of those products. What I need to narrow down the results are orders that have those products altogether, not only one or two of the products. We are attempting to get counts of a Work Flow that we have recently implemented

I am a bit confused. There is an order table. A row in that table is an order. It contains the order ID (`orderid`). But it also contains a product ID (`fk_product_id`). So one order contains one product. How then can one order contain six different products at the same time? For this you'd need an m:n relation (a product table, an order table and a product_order table). — Thorsten Kettner, Feb 08 '21 at 17:13

score 0 · Answer 1 · answered Feb 08 '21 at 17:06

You can use aggregation and having:

SELECT o.order_date, o.price AS revenue, o.cost,
       o.id AS orderid, o.fk_order_status_id, o.date_started, o.date_completed
FROM orders o JOIN
     file f 
     ON f.id = o.fk_file_id JOIN
     product p 
     ON p.id = o.fk_product_id JOIn
     product_product pp 
     ON pp.fk_product_id = o.fk_product_id
WHERE o.fk_product_id IN (66, 8, 6, 21, 11) 
GROUP BY o.order_date, o.price, o.cost,
       o.id, o.fk_order_status_id, o.date_started, o.date_completed
HAVING COUNT(DISTINCT o.fk_product_id) = 5;  -- number of products in the list

I am working with Google Data Studio and PostGRESql, our database is new, and can be challenging. Basically in our business our clients have their own files, which then contain different orders. Each order is for a different product, but are linked back to the file. — Jordyn, Feb 08 '21 at 17:23

Selections through SQL

1 Answers1