Qfyilyi

I have two tables, namely itemOrders and itemDelivered.

itemOrders

+-------+---------+--------+

| id    | orderid | itemid |

+-------+---------+--------+

| 1     | 1       | 1      |

| 2     | 1       | 2      |

| 3     | 2       | 1      |

| 4     | 2       | 2      |

| 5     | 3       | 1      |

| 6     | 3       | 2      |

+-------+---------+--------+

And

itemDelivered

+-------+-------------+--------+

| id    | orderId     | itemid |

+-------+-------------+--------+

| 1     | 2           | 2      |

| 2     | 3           | 2      |

| 3     | 2           | 1      |

+-------+-------------+--------+

From the above scenario I want all those distinct items whose max orderId in the table itemDelivered is less than max orderId in the table itemOrders.

In the above example I should get itemid 1 as the result, as it's max orderid is 2 in table itemDelivered, which is less than its max orderid in table itemOrders which is 3.

I wrote the following query but it gives me both the items, 1 and 2 as item No. 2 doesn't have orderId 1 in itemDelivered table.

SELECT DISTINCT( itemid )

FROM   itemorders

WHERE  orderid NOT IN (SELECT orderid

                       FROM   itemdelivered)  

You can LEFT JOIN between the two table using itemid, and GROUP BY on the itemid.

Eventually use HAVING clause to consider only those itemid values, where MAX(itemdelivered.orderid) < MAX(itemorders.orderid)

View on DB Fiddle

SELECT io.itemid

FROM itemorders AS io 

LEFT JOIN itemdelivered AS id 

  ON id.itemid = io.itemid 

GROUP BY io.itemid 

HAVING MAX(id.orderid) < MAX(io.orderid) 

    OR MAX(id.orderid) IS NULL

Result

| itemid |

| ------ |

| 1      |

Ok, manage to write a query which gives the desired output.

SELECT io.itemid 

    FROM itemorders as io 

    LEFT JOIN itemdelivered AS id ON io.orderid = id.orderid AND io.itemid = id.itemid 

    WHERE id.itemid IS NULL 

    HAVING MAX(id.orderid) IS NULL 

    ORDER BY io.id