[QODBC-Desktop] How to do an Inner JOIN in QODBC
Posted by brad waddell on 12 March 2009 05:21 PM
|
|
Inner JOINs and Left JOINs in QODBCNote: Inner Joins, and Left Joins can be done with the following syntax (for a right join, reverse the table order and use a left join). ExamplesSELECT * FROM {OJ Invoice INNER JOIN Customer ON (Invoice.CustomerRefListID = Customer.ListID)} Left Join SELECT Invoice.CustomerRefListID, Customer.fullname,Customer.listid FROM Customer Left outer JOIN Invoice ON Invoice.CustomerRefListID = Customer.ListID Other HintsPerformance Related If you can include a starting value or range of values (> and <) for TxnDate or TimeModified as the first option in your WHERE statement, you can improve your performance many times over. Also, there is a pretty big-time penalty for using ORDER BY. If you can live without it, you can improve your performance. It is also faster to specify only the fields you require than a wildcard. Parentheses Required Parentheses are required when more than two tables are being joined: SELECT COUNT(TxnID) FROM (Invoice INNER JOIN SalesRep ON Invoice.SalesRepRefListID = SalesRep.ListID) | |
|
QODBC is supporting Left join also.
I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.
We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Yes .net does provides Data Table relationship and is faster.
It fetches all the records and then applies the relationship.
Initial time it little longer but overall performance will be good, if you are working on the derived dataset.
Please find below example, It is already in the article above.
Parentheses are required when there are more than two tables being joined:
SELECT COUNT(TxnID) FROM (Invoice INNER JOIN SalesRep ON Invoice.SalesRepRefListID = SalesRep.ListID)
INNER JOIN Employee ON SalesRep.SalesRepEntityRefListID = Employee.ListID
Please refer below sample query which will joining four table. You can change it according to your requirement.
SELECT Customer.Name from Customer,Invoice,Template,Terms where Customer.ListID=Invoice.CustomerRefListID and Invoice.TemplateRefListID = Template.ListID and Invoice.TermsRefListID = Terms.ListID
SELECT
Customer.Name,
max( Invoice.TxnDate ) AS Max_Inv_Dte,
max( Estimate.TxnDate ) AS Max_Est_Dte,
max( ReceivePayment.TxnDate ) AS Max_Pmt_Dte
FROM
Customer
LEFT OUTER JOIN Invoice
ON Customer.ListID = Invoice.CustomerRefListID
LEFT OUTER JOIN ReceivePayment
ON Customer.ListID = ReceivePayment.CustomerRefListID
LEFT OUTER JOIN Estimate
ON Customer.ListID = Estimate.CustomerRefListID
WHERE
Customer.IsActive = 1
GROUP BY
Customer.Name
HAVING
max(Invoice.TxnDate) < {fn CURDATE()}-365
AND max(ReceivePayment.TxnDate) < {fn CURDATE()}-365
AND max( Estimate.TxnDate ), < {fn CURDATE()}-365
ORDER BY
Customer.Name ASC
Please try below query & share outcome:
SELECT
Customer.Name,
max( Invoice.TxnDate ) AS Max_Inv_Dte,
max( Estimate.TxnDate ) AS Max_Est_Dte,
max( ReceivePayment.TxnDate ) AS Max_Pmt_Dte
FROM Customer,Invoice,ReceivePayment,Estimate WHERE
Customer.ListID = Invoice.CustomerRefListID
And
Customer.ListID = ReceivePayment.CustomerRefListID
And
Customer.ListID = Estimate.CustomerRefListID
And
Customer.IsActive = 1
GROUP BY
Customer.Name
HAVING
max(Invoice.TxnDate) < {fn CURDATE()}-365
AND max(ReceivePayment.TxnDate) < {fn CURDATE()}-365
AND max( Estimate.TxnDate ) < {fn CURDATE()}-365
ORDER BY
Customer.Name ASC
If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit
I kindly request you to share more information about the issue you’re facing, so that we can locate the problem quickly.
We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About (Start>>All Programs>> QODBC Driver for use with QuickBooks>> QODBC Setup Screen >> About Tab )
2) Screenshot of the issue you’re facing.
3) Share the SQL statement you’re using.
Share Entire Log Files as an attachment in text format from
4) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
5) QODBC Setup Screen -- > Messages -- > Review SDK Messages