0

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;
Zoay
  • 55
  • 5
  • MySQL does not support `FULL OUTER JOIN`. Is MySQL really your database? – Tim Biegeleisen May 07 '20 at 10:35
  • Ah sorry my bad, should just be SQL. Edited now. – Zoay May 07 '20 at 10:37
  • 1
    SQL is a language, not DBMS... – Akina May 07 '20 at 10:38
  • *if I try to search by **user.orders.reference** instead it just timeouts* - a point instead of underscore? a typo? – Akina May 07 '20 at 10:40
  • A typo, the actual query I run is much much longer but for privacy and friendly reasons I rewrote it to be much shorter while maintaining the core structure of it. – Zoay May 07 '20 at 10:44
  • 1
    Are you even sure you want full outer joins? It seems to me you want left outer joins. Anyway, as these tables are linked via `orderid`, it's easy peasy to perform these joins when you give the query one orderid. If you give it something else instead, all these joins become way more expensive. – Thorsten Kettner May 07 '20 at 10:48
  • I might suggest you rewriting your query thinking what is what you want each row to represent. Do decisionlog, refunds, notifications only have one row per orderid? Does e user_orders.reference have many user_orders.orderid? If so, your full outer join might be endless... – CarlosSR May 07 '20 at 10:49
  • Aah okay, that probably explains it then. The problem is that the decisionlog, refund and notifications doesn't exist for some orders, while they do for others. If I do a regular JOIN on them and try to search on an orderID where they are not present, it will give me some error. With the FULL OUTER JOIN it will still find the order, but return empty values for those rows.. Not sure how well I'm explaining it, still quite new to SQL and I have no access to modify the DB itself, only to collect info from it. – Zoay May 07 '20 at 10:56
  • If you want orders including those without decisionlogs, refunds, and notifocations, you should left outer join these. If you also want decisionlogs, refunds, and notifocations without orders then you would full outer join. But do such even exist? – Thorsten Kettner May 07 '20 at 11:06

1 Answers1

1

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;
CarlosSR
  • 1,145
  • 1
  • 10
  • 22
  • Thanks for the explanation, got it working now! Had to make them all LEFT JOIN though, if I kept transfers/orders/users as INNER JOIN it still just timed out for some reason. But when using LEFT JOIN on all of the tables I get instant results. – Zoay May 07 '20 at 11:17
  • As a best practice, try to identify always, before writing the query, what is what you want each row to represent. – CarlosSR May 07 '20 at 11:34