CODEWITHSUNDEEP
sql
mrtn_
mrtn_

Reputation: 41

How to select data based on two tables

I have two tables and want to select only the ones where a certain status is 'complete' in the second table. The most important factor is that all shipments need to be completed.

First Table (orders) includes a unique order id and several cells with customer details. For example

Order_id | Name

1001 | John
1002 | Paula
1003 | Ben

The second table (shipments) has all the items a customer ordered and the status whether they were delivered e.g.

Order_id | Shipment_number | Status

1001 | 8004 | complete
1001 | 8003 | processing
1002 | 8005 | complete
1003 | 8008 | processing
1003 | 8007 | processing
1003 | 8009 | complete

I tried it with the following code but unfortunately the results show all order ids where at least one of the associated shipments is 'complete'.

SELECT
  order_id,
  name
FROM orders
INNER JOIN shipments ON orders.order_id = shipments.order_id 
WHERE
shipments.status = 'complete';

I'm pretty new to SQL and really struggling with this. Thanks in advance :)

Upvotes: 4

Views: 382

Answers (5)

Brian Hoover
Brian Hoover

Reputation: 7991

Generally, the best way to do this is to have a level at the order itself that indicates whether or not the order is completely shipped.

But, this would work although it might not scale really well. To get it to scale better, you would have to do another join BACK to orders in the subqueries to limit the orders that you are looking through.

select order_id, name from 
orders JOIN 
   (select order_id, count(*) from shipments where status = 'complete' group by order_id
    INTERSECT 
    select order_id, count(*) from shipments group by order_id)
    on order_id

Upvotes: 0

scrappedcola
scrappedcola

Reputation: 10572

SELECT ship.order_id, ord.name
    FROM shipments as ship
    left join orders as ord on
       ord.order_id = ship.order_id 
where ship.status = 'complete'

Upvotes: 0

phil
phil

Reputation: 707

You could try this

SELECT order_id FROM <orders>
WHERE order_id NOT IN (SELECT order_id FROM <shipments> WHERE status <> 'complete')

Even though, depending on your system I would consider adding a field in order table (effectively de-normalizing it, but benefits/drawbacks depend on how often you need this information) and update it with current order status.

If you need this information often, I might be worth it.

Upvotes: 1

Desmond Zhou
Desmond Zhou

Reputation: 1399

You can use nested queries to do this:

  1. Write a query to take a count of the shipments.order_id group by order_id, this gives you the total shipments for each order, select the order_id and the count.

  2. Write a query to take a count of the shipments.order_id group by order_id where shipments.status = 'complete', this gives you the completed shipments for each order, select the order_id and the count.

  3. join the result of 1 and 2 with order on order id where (1.count = 2.count), select the name and the order_id.

Upvotes: 1

Dylan Smith
Dylan Smith

Reputation: 22245

SELECT Orders.OrderID 
FROM orders 
WHERE NOT EXISTS (SELECT OrderID 
                  FROM shipments 
                  WHERE status != 'complete' AND 
                        shipments.OrderID = Orders.OrderID)

Upvotes: 1

Related Questions