Knowledgebase
[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 QODBC

Note: 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).

Examples

SELECT * FROM {OJ Invoice INNER JOIN Customer ON (Invoice.CustomerRefListID = Customer.ListID)}

or

SELECT *FROM 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 Hints

Performance 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)
INNER JOIN Employee ON SalesRep.SalesRepEntityRefListID = Employee.ListID

(193 vote(s))
Helpful
Not helpful
Comments (10)
Ben
15 August 2012 02:00 AM
Can only do Inner Join. Left Joins do not work
Jack
08 July 2014 11:09 AM
Hi Ben,

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
Sherdz Buckland
08 August 2013 05:59 AM
If your application is .net, it is much faster to load the tables into a dataset and do the join there, rather than doing the join in QODBC
Rajendra Dewani
21 September 2013 07:33 AM
Hi,
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.
Paul DesRoches
30 September 2014 04:23 AM
What's the syntax for joining three tables? I've tried a lot of different options but nothing seems to work.
Jack
30 September 2014 10:29 AM
Hi Paul,

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

Paul DesRoches
30 September 2014 11:26 PM
Sorry I meant three inner joins, four tables.
Jack
01 October 2014 10:35 AM
Hi Paul,

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
Jim F.
24 April 2015 07:55 PM
I am trying to generate a list of active Customers that don't have any Estimate, Invoice, or Payment activity in the last year so I can set them as inactive. The way I decided to do that is with three Left Outer joins between Customer and the three transaction tables, but I can't get the syntax correct. Here is what I have:

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
Jack
04 May 2015 10:44 AM
Hi Jim,

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
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).