"Combine" FULL OUTER JOIN and NOT IN?

Question

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!

score 1 · Answer 1 · answered Apr 17 '17 at 12:10

1

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

answered Apr 17 '17 at 12:10

j-bin

671
7
9

Yes Jben thanks for your improved answer! Yea Terje gave the wrong table name but the logic is right, and your answer just makes it a 110% perfect! And thanks for your hint on selecting the perticular user as well! :D You are just awesome man! – Hang Apr 17 '17 at 19:23
Hi @j-bin actually I think this code may have bugs? Today I tried to test this query with multiple users taking the orders at the same time. I use the shop owner account to create a fresh new order and then run this query I can see this new order is on every user's page, but after one of the users took the order, say the one with userId=3, then other users like userId=2or4 will not see this order again, however this order continues to show at the userId=3's page and the status is shown correctly. I tried to modify the code by taking ot.user_id = ? out to the where clause and make it like: – Hang Apr 19 '17 at 22:48
WHERE o.isClosed = 0 AND ot.user_id = 2/4 AND ( (ot.orderStatus IS NULL) OR (ot.orderStatus NOT IN (4,5))), however this still returns an empty array. I also tried putting this user_id limitation to any other place with no luck. Could you still take a look at it I would really appreciate it! :D – Hang Apr 19 '17 at 22:52
I guess the reason that once someone has taken an order from this orderId then this orderId will be recorded in the ot table, so if we still make it "ON o.orderId = ot.orderId" even though we put a ot.orderId IS NULL or ot.orderStatus IS NULL since it's never a NULL anymore so the there will be no result? – Hang Apr 19 '17 at 22:56
The test of this query goes perfectly if there is only one user takes this order, but after this one user has taken the order, if we let userId equals other users' userId then this will return an empty set – Hang Apr 19 '17 at 22:59

Terje D. · Accepted Answer · 2017-04-17T19:48:00.053

0

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

edited Apr 17 '17 at 19:48

answered Apr 17 '17 at 11:23

Terje D.

6,250
1
22
30

Thank you Terje! That's exactly what I want! I was thinking to use LEFT JOIN but I just didn't know the "IS NULL" clause. That's a key thing I learned from you. Thanks again for solving my problem! :D – Hang Apr 17 '17 at 19:22
I have now corrected the table name (orderTaken, was misspelled as orderStatus). – Terje D. Apr 17 '17 at 19:49
That makes it more precise. Thanks again Terje! – Hang Apr 17 '17 at 21:10
Hi Terje D. could you take a look at my comments under j-bin's answer as well? Your provided query will also return an empty set for other users after one of the users has taken the order... :( Say after the user with userID=3 has taken the order, if I run your query with userId=4, then it will return an empty set. – Hang Apr 19 '17 at 23:38
What do you want to show for orders taken by several users (_e.g._ 2, and 3), but not the current one (4)? Do you want just one line showing that the order is not taken by user 4, or one line for each user who has taken the order, showing the related orderStatus for each of them? – Terje D. Apr 20 '17 at 09:58

"Combine" FULL OUTER JOIN and NOT IN?

2 Answers2