CODEWITHSUNDEEP
mysqljoinwhere-clausenotinfull-outer-join
Hang Chen
Hang Chen

Reputation: 873

"Combine" FULL OUTER JOIN and NOT IN?

I have two tables:

+-----------------------+
| Tables_in_my_database |
+-----------------------+
| orders                | 
| orderTaken            | 
+-----------------------+

In orders, there are attributes

orderId, orderName, isClosed and orderCreationTime.

In orderTaken, there are attributes

userId, orderId and orderStatus.

Let's say when

orderStatus = 1 --> the customer has taken the order
orderStatus = 2 --> the order has been shipped
orderStatus = 3 --> the order is completed
orderStatus = 4 --> the order is canceled
orderStatus = 5 --> the order has an exception

Basically the mechanism of my project is running like: A user with a unique userId will be able to take an order from the web page, where each order has its own unique orderId as well. After taken, the orderTaken table will record the userId, orderId and initially set orderStatus = 1. The shop then update the orderStatus based on various situations. Once the shop has updated isClosed = 1 then this order wouldn't be displayed at all no matter the user has taken it or not(not make sense but it's just a isClosed == 0 in the query).

Now, I want to construct a web page that will show both the new orders that the user hasn't taken yet (which should be the orders that their orderIds are not recorded in the orderTaken table under this user's userId), and the orders that the user has already taken with the orderStatus shown BUT the orderStatus IS NOT 4 or 5, group by orderCreationTime DESC (yea, maybe not make sense if I don't have a orderTakenTime but let's keep it that way), like:

OrderId 4
Order Name: PetPikachu
orderStatus = 1
CreationTime: 5am

OrderId 3
Order Name: A truck of hamsters
orderStatus = 3
CreationTime: 4am

OrderId 2
New order
Order Name: Macbuk bull
CreationTime: 3am

OrderId 1
Order Name: Jay Chou's Album
orderStatus = 2
CreationTime: 2am

I have this query written based on the knowledge I've learned:

SELECT * FROM orders A WHERE A.isClosed == '0' FULL OUTER JOIN orderTaken B  WHERE B.userId = '4' AND (B.orderStatus<>'4' OR B.orderStatus<>'5') ORDER BY A.orderCreationTime DESC;

Apparently this query doesn't work, but I'm afraid to have a 

ON A.orderId = B.orderId

since then the table returned will eliminate the new orders that the orderId hasn't been recorded in orderTaken B. I've also tried a NOT IN clause like

SELECT * FROM orders A WHERE A.isClosed = '0' AND A.orderId NOT IN (SELECT orderId FROM orderTaken B WHERE B.userId = '$userId' AND (B.orderStatus='4' OR B.orderStatus='5')) ORDER BY creationTime DESC;

This query works but it doesn't have the field orderStatus from orderTaken B in the returned table. I was thinking to add another JOIN orderTaken B clause after this query to get the fields from B but I think that's not a good way to write a query.

I just wanna kinda combine "NOT IN" and "FULL JOIN". Can anybody help me out? Thanks!

Upvotes: 0

Views: 53

Answers (2)

Terje D.
Terje D.

Reputation: 6315

You seem to want to find the records in orders that is not assigned to an user (i.e. does not have a related record in orderTaken) plus the ones that are assigned to an user, but where the orderStatus is not 4 or 5.

Then a full outer join is not needed as there will be no records in orderTaken without a related record in orders. A Left inner join can be used to find all the records from orders, an onclause will include data from the related records from orderTaken and the where clause can then filter out orders taken by other users, or where orderStatus is 4 or 5:

SELECT o.*, ot.userID, ot.orderStatus
FROM orders o
LEFT JOIN orderTaken ot
ON ot.orderID = o.orderID
WHERE o.isClosed = 0
AND (ot.userID IS NULL OR ot.userID = $userID AND ot.orderStatus NOT IN (4,5))
ORDER BY o.creationTime DESC

Upvotes: 0

j-bin
j-bin

Reputation: 691

Just like @terje-d said, what you need is LEFT JOIN. Updated it with the the original table names and fixed the $userId filter.

  • For all open orders and incomplete orders.
   SELECT o.`orderId`, 
          o.`orderName`,
          ot.`orderStatus`,
          o.`orderCreationTime`
     FROM orders o 
LEFT JOIN orderTaken ot
       ON o.orderId = ot.orderId
    WHERE o.isClosed = 0
      AND (
            ot.orderId IS NULL
         OR ot.orderStatus NOT IN (4,5)
      )
 ORDER BY o.`orderCreationTime` DESC
  • For all open orders and incomplete orders for a particular user
   SELECT o.`orderId`, 
          o.`orderName`,
          ot.`orderStatus`,
          o.`orderCreationTime`
     FROM orders o 
LEFT JOIN orderTaken ot
       ON o.orderId = ot.orderId
    WHERE o.isClosed = 0
      AND ( ot.orderStatus IS NULL
         OR (
                ot.user_id = ?
            AND ot.orderStatus NOT IN (4,5)
         )
      )
 ORDER BY o.`orderCreationTime` DESC

Upvotes: 1

Related Questions