Creating a query with unrelated tables
I am trying to create a query to do trend analysis on breakdown rate (No. of Service Calls created) and costing (based on invoices to customer) over a time period for an item.
I have drawn data from SCL4, OSCL, and OINV, but realised there is no link between the Service tables and OINV. Is it possible to link up tables that are unrelated?
Appreciate any form of assistance
Johan Hakkesteegt replied
When you use bridging tables to connect unrelated tables, you need to look into the JOIN statement.
The B1 Query Generator tool will always use INNER JOIN, which means that only results from the joined tables will be shown where the common denominator can be found in both tables. In your case, where you have joined 4 different tables, the common denominator needs to be present in all 4 tables. This is very unlikely to ever be true, and thus you get an empty result set.
Please try LEFT OUTER JOIN, where the bridging table should preferably be the first. So for example something like this:
, SUM(ISNULL(T1.DocTotal, 0)) AS DocTotalInvoice
, SUM(ISNULL(T2.DocTotal, 0)) AS DocTotalCreditNote
FROM OCRD T0
LEFT OUTER JOIN OINV T1 ON T0.CardCode = T1.CardCode
LEFT OUTER JOIN ORIN T2 ON T0.CardCode = T2.CardCode
GROUP BY T0.CardCode
About the UNION statement, please note that if you use UNION, you will get all results from the upper query, and only results that differ from the bottom query. With UNION ALL, you will get all results from both queries, and you could get "doubles".
The nice thing about UNION ALL is that you can also use it to add a total row at the bottom.
SELECT DocNum, DocTotal
SELECT 'Total', SUM(DocTotal)