So I have a query that I run that basically looks like the one below (simplified it though for easier viewing). When I search on user_orders.orderID it works flawlessly, but if I try to search by user_orders.reference instead it just timeouts.
Now I assume that this has something to do with some of the tables that I have joined not having the reference number in them, only the orderID is present in all of them. However it would greatly simplify my work if I could search on the reference number directly, instead of the orderID.
Any advice on how to solve it?
EDIT:
Solved now, thanks everyone! It was indeed the FULL OUTER JOIN that was causing the problems, didn’t fully understand what it did compared to a regular JOIN.
SELECT user_orders.reference, user_orders.orderid, transfers.username, notifications.datestamp, orders.refund, users.acac, refunds.state, decisionlog.data FROM user_orders FULL OUTER JOIN decisionlog ON user_orders.orderid = decisionlog.orderid FULL OUTER JOIN refunds ON user_orders.orderid = refunds.orderid FULL OUTER JOIN notifications ON user_orders.orderid = notifications.orderid JOIN transfers ON user_orders.orderid = transfers.orderid JOIN orders ON transfers.orderid = orders.orderid JOIN users ON transfers.username = users.username WHERE user_orders.orderid = xxx;
Advertisement
Answer
As suggested in the comments, if you do not have an orderid in any of decisionlog, refunds, notifications tables, this query will return a null for notifications.datestamps, refunds.state, decisionlog.data
, but it will not give you an error.
Plus, take into account that using JOIN (INNER JOIN) will return you only orderid’s that are on the transfer and order table and whose users are found at users table
SELECT user_orders.reference, user_orders.orderid, transfers.username, notifications.datestamp, orders.refund, users.acac, refunds.state, decisionlog.data FROM user_orders LEFT JOIN decisionlog ON user_orders.orderid = decisionlog.orderid LEFT JOIN refunds ON user_orders.orderid = refunds.orderid LFET JOIN notifications ON user_orders.orderid = notifications.orderid INNER JOIN transfers ON user_orders.orderid = transfers.orderid INNER JOIN orders ON transfers.orderid = orders.orderid INNER JOIN users ON transfers.username = users.username WHERE user_orders.reference = xxx;