MySQL Forums
Forum List  »  General

Please help with complex where
Posted by: Zero-G. _
Date: December 04, 2018 04:22AM

Hey
I am using mySQL 5.7 and have 3 tables, which are related.
I post only a small portion of fields to explain my needs

1) Customer Table

Field: Id BigInt
Field: Name as CustomerName

2) Order Table
Field Id BigInt
Field CustomerId BigInt (FK to CustomerTable)
Field OrderDate Date

3) OrderDetails Table
Field Id BigInt
Field OrderId as BigInt (FK to OrderTable)
Field Assignment Integer


The complex here is, that a "complete order" would mean, that in OrderDetails Table are two rows with
One Row must have an assignment with value 1
One Row must have an assignment with value 2

It is allowed to save "incomplete order", that means, that in OrderDetails there is only associated row with either the value 1 or 2

Now, I try to find all customers, which have the last complete order within a specific date range.

What I did so far:

SELECT
Customer.*
FROM Customer WHERE ID IN (SELECT CustomerId FROM Order
JOIN OrderDetails a1
ON a1.OrderId = Order.id
JOIN OrderDetails a2
ON a2.OrderId = Order.id
WHERE a1.assignment=1 AND a2.assignment=2
ORDER BY Order.OrderDate DESC LIMIT 1)


The problem here is, that I first would have to limit the orders to be sure, that this is the last "complete order" and the query the date of this result.

But Here I stuck. because when I use the date within the where clause, then it must not be the last "complete order".


Hope you get my issue. - THX a lot - Have a nice day

Options: ReplyQuote


Subject
Written By
Posted
Please help with complex where
December 04, 2018 04:22AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.