Question OleDbCommand String (Syntax Error in From Clause)

[ncastillo]

I'm creating an app in Visual Studio 2008 Standard and I'm trying to create a query with a subquery for an MS Access database (.accdb). I'm getting the error 'Syntax Error in From Clause'. Please see my code below. Would appreciate any help. Thanks!

Dim cmdLoanerTransactions As New OleDbCommand("SELECT tblLoaners.Loaner, tblLoaners.Code, tblLoaners.Model," & _
" tblTransactions.CheckedOutTo, tblTransactions.DueDate," & _
" tblTransactions.Comments, tblTransactions.Analyst" & _
" FROM tblLoaners LEFT JOIN (SELECT CheckedOutTo, DueDate, Comments, Analyst" & _
" FROM tblTransactions ON tblLoaners.ID = tblTransactions.Loaner (WHERE CheckedInDate IS NULL))" & _
" WHERE Active = 'Active'", conn)

 

[IanRyder]

Hi and welcome to the Forum,

When designing queries I would always recommend that you start with using the inbuilt Query Designers to get started with your query since these do a lot of the donkey work for you.

In your query you have got your join and select statements all jumbled up as well as missing a Field name in the Join statement. This can be more simply be written as:-

SELECT tblLoaners.IDNo, tblLoaners.Loaner, tblLoaners.Code, tblLoaners.Model, tblTransactions.CheckedOutTo, tblTransactions.DueDate, tblTransactions.Comments, tblTransactions.Analyst
 
FROM tblLoaners INNER JOIN tblTransactions ON tblLoaners.IDNo = tblTransactions.LoanerIDNo
 
WHERE (((tblTransactions.CheckedInDate) Is Null) AND ((tblTransactions.Active)="Active"));

Note, this was written with Access's query designer.

Hope that helps.

Cheers,

Ian

 

[Herman]

The above is almost right, and illustrates why relying on designers is a bad idea... You should learn to understand the language first and then use the designers to make your life easier, although once you know how to write SQL you won't want to use the designer anyways. The only problem with the above query is that you want a LEFT JOIN, not an INNER JOIN, but it is also very verbose... Also I suspected Active was a field of Loaners while Ian thought it was Transactions, but that is anyone's guess. Use aliases whenever you join, make it clear which field belongs where. Also if you are going to split a string across multiple lines use XML literals, much easier to read and less effort.

Dim sql As String = <s> SELECT l.Loaner, l.Code, l.Model, t.CheckedOutTo, t.DueDate, t.Comments, t.Analyst
                        FROM tblLoaners l
                        LEFT JOIN tblTransactions t ON l.ID = t.Loaner
                        WHERE t.CheckedInDate IS NULL
                          AND l.Active = 'Active';
                    </s>

 

[IanRyder]

Morning Herman,

The above is almost right, and illustrates why relying on designers is a bad idea...

I will agree to disagree with you on that one but that's just a personal choice. The reason why I used an Inner Join here in my example is, as you rightly said, I have assumed that the fields Active and CheckedInDate belong to the Transactions table and since the selection criteria is based on the contents of the Right Hand Table then a Left Join becomes unnecessary. If however the field Active belongs to the Loaners Table then a Left Join does become valid as you rightly imply with your example so long as the OP also wants to return those Loaner records that do not have any records in the Transactions table.

Cheers,

Ian

 

[Herman]

You cannot know if a left join or inner join is necessary and the results are drastically different. A left joins will return Loaner rows even if Transactions has no row matching, inner join will not return rows that have no match in Transactions. Also there is confusion about what the word LEFT means in a LEFT JOIN. The left table is the one referenced above the join, NOT the one on the left side of the equal sign. The RIGHT table is the one referenced after the join.

Above, the LEFT table (the "selection criteria") is tblLoaner, in all cases. If you wanted all records that matched en entry in Transactions, it would have been a RIGHT join. It does not matter if the ON clause references t or l on the left or right side of the equals.

Also the word OUTER is completely optional above. LEFT, RIGHT, and FULL joins are three OUTER joins, there is no INNER LEFT or INNER RIGHT.

 

[ncastillo]

Hi all,

Thank you so much for your replies! Sorry for not being more specific. Yes, the field Active does belong to tblLoaners and the field CheckedInDate belongs to tblTransactions. I need to be able to show all tblLoaner records not only the ones that are equal to tblTransactions. I also need a WHERE clause on each table. I originally had tried the code below that Herman suggested and was unable to get all of the records in tblLoaners. I started thinking that this was a filter issue and that I need a subquery for tblTransactions instead to try and separate the two WHERE clauses.

Dim sql As String = <s> SELECT l.Loaner, l.Code, l.Model, t.CheckedOutTo, t.DueDate, t.Comments, t.Analyst
FROM tblLoaners l
LEFT JOIN tblTransactions t ON l.ID = t.Loaner
WHERE t.CheckedInDate IS NULL
AND l.Active = 'Active';
</s>

 

[Herman]

If you want all the records from Loaners, no matter what is in Transactions (including CheckedInDate) then just remove the CheckedInDate field from the where clause.

If you want all the records from Loaners, and only matching records from Transactions where CheckedInDate is Null, then as you said you either join with a subquery or make the CheckedInDate field part of the join relationship:

SELECT l.Loaner, l.Code, l.Model, t.CheckedOutTo, t.DueDate, t.Comments, t.Analyst
FROM tblLoaners l
LEFT JOIN ( SELECT CheckedOutTo, DueDate, Comments, Analyst, CheckedInDate, Loaner
            FROM tblTransactions
            WHERE CheckedInDate IS NULL ) t ON l.ID = t.Loaner
WHERE l.Active = 'Active';


SELECT l.Loaner, l.Code, l.Model, t.CheckedOutTo, t.DueDate, t.Comments, t.Analyst
FROM tblLoaners l
LEFT JOIN tblTransactions t ON l.ID = t.Loaner
                           AND t.CheckedInDate IS NULL
WHERE l.Active = 'Active';

Both forms should work the same, personally I prefer the first, it's more explicit exactly what you are after, and will probably make constructing indexes easier for you afterwards. I *think* I also remember a paper where it was discussed that the SQL compiler does a better job at optimization of the query execution plan when using subqueries rather than flat joins, but I might be mistaken.

 

[ncastillo]

Thanks! I'll give that a try.

 

[ncastillo]

Awesome! The first option worked for me. Thank you!