sub query

  • Thread starter Thread starter dave h
  • Start date Start date
D

dave h

Hi,

Can anyone tell me why this sql returns an error of "too few parameters.
Expected 1"

mySQL = "SELECT rd.reservedetailID, rd.inventoryID, inv.copynumber,
lfr.loanDetailID"

mySQL = mySQL & " FROM reservedetail_215 as rd, libraryInventory_33 as
inv "

mySQL = mySQL & " WHERE rd.inventoryID = inv.inventoryID "

mySQL = mySQL & " AND rd.reservedetailID = 42 "

mySQL = mySQL & " AND lfr.loandetailID IN (SELECT loandetailID FROM
loanForReserve_37 as lfr"

mySQL = mySQL & " WHERE lfr.reservedetailID = rd.reservedetailID)"

There are 3 tables involved. Matching the libraryInventory table to the
reserveDetail table for ID 42 is no problem. My goal is to effect an outer
join with the subquery. If a loanForReserve row does exist for ID 42, then
I need the loanDetailID field, otherwise I need that field to be blank or
zero. Right now I'm simply stuck with this syntax error.
 
Hi,


lfr is defined in the sub-query and is not available in the outer query. As
in VB/VBA:



Option Explicit
Private x As Long

Public Sub y()
Dim z As Long
....
End Sub


where x is available everywhere in this module, but z is only available in
the subroutine defining it.




Hoping it may help
Vanderghast, Access MVP
 
Hi Dave,

I think that the main problem with the syntax is that the lfr table is not
in your FROM clause in your main query, but it is trying to compare the value
from a field in that table to the results of a subquery. If you want to
check to see if the lfr.loandetailID exists in the subquery, it needs to be
part of the main query FROM clause.

There are a few ways that you can get the outer join that you are looking
for. In Access, the join types are usually defined in the FROM clause, along
with the definition of the related fields using ON.

Using Access sql, your syntax could be revised to something like:

SELECT ...
FROM (reservedetail_215 AS rd INNER JOIN libraryInventory_33 AS inv ON
rd.inventoryID = inv.inventoryID) LEFT JOIN loanForReserve_37 AS lfr ON
rd.reservedetailID = lfr.reservedetailID
WHERE ...

If your tables have a 1:1 relationship, that's probably all you will need to
do to get what you want. If there is a match, it will be displayed, if not,
it will show null.

But, if you have a 1:many relationship with the loanForReserve_37 table, it
would probably be better to remove it altogether from the SELECT, and use a
subquery instead to look for the first match. In that case, you would add
the subquery to the SELECT list, such as:

SELECT rd.reservedetailID, rd.inventoryID, inv.copynumber,
(SELECT First(lfr.loanDetailID) FROM loanForReserve_37 AS lfr WHERE
lfr.reservedetailID = rd.reservedetailID)
FROM .....

You could use Max, or Min, in place of First if you would want the highest
or lowest number if there are more than one possible matches. In any case,
the subquery would return a null if no matches were found. You could also
use this syntax even if you have a 1:1 relationship, but the outer join would
probably be faster.

Hopefully that will help. Post back if I misunderstood what you are trying
to do.

-Ted Allen
 
Hi Ted,

You are an extraordinary guy to both pick up on the details of my question
so very well and also give me a perfect solution. I only tried your first
suggestion as it worked perfectly. I'll try the other one also just for the
learning value.

Thanks very much. Dave H.
 
dave h said:
Hi Ted,

You are an extraordinary guy to both pick up on the details of my question
so very well and also give me a perfect solution. I only tried your first
suggestion as it worked perfectly. I'll try the other one also just for the
learning value.

Thanks very much. Dave H.
Click to expand...
 
Thanks for the very kind compliment Dave. It was my pleasure to help and I'm
glad it worked for you.

P.s. - Please ignore the accidental double post if there is a blank message
along with this one, I think I may have accidentally hit the post button on a
blank reply.

-Ted Allen
 
Back
Top