I have the following query for my crystal reports;
SELECT DISTINCT T0.custmrName, T0.callID,T0.BpShipAddr, T1.Name, T0.createDate,T0.subject,T0.manufSN, T0.internalSN,T0.itemCode,T0.itemName, CAST(T0."resolution" AS varchar(MAX)),T0.BPContact, T0.BPCellular, T2.firstName, T2.lastName FROM OSCL T0 LEFT JOIN OSCT T1 ON T0.callType = T1.callTypeID LEFT JOIN OHEM T2 ON T0.technician = T2.empID LEFT JOIN SCL4 T3 ON T3.SrcvCallID = T0.callID
Now i also need to include either the quote number from a linked quote or the sales order from a linked sales order. If a linked quote does not exist then it should get the DocNum from the sales order, however if a quote exists it will always grab it from the linked quote (and not the sales order if both exist).
My issue is that I need to use INNER JOIN for both documents otherwise i get an extra record if more than one exist, if i use inner joins for both though then it displays no information. What would be the best way of going about this?
My current solution (which is a horrendous one at that) was to use subreports for both, but then if they both exist then it shows both which is not ideal.
As always any help is appreciated.