Obtaining single records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables with a many-to-many relationship, and a junction table.
They are tblOwners, tblLots, and tblJunction. I would like to print one
mailing lable for each owner who purchased at least one lot in 2003.
(Purchase date is a field in tblLots.) How can I get such a list without
creating duplicates? (Access 2003)
Thanks for your assistance.
 
SELECT tblOwners.*
FROM tblOwners
WHERE tblOwners.OwnerID IN
(SELECT OwnerID
FROM tblJunction
INNER JOIN tblLots
ON tblJunction.LotID = tblLots.LotID
WHERE Year(tblLots.PurchaseDate) = 2003)

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
George R said:
I have two tables with a many-to-many relationship, and a junction table.
They are tblOwners, tblLots, and tblJunction. I would like to print one
mailing lable for each owner who purchased at least one lot in 2003.
(Purchase date is a field in tblLots.) How can I get such a list without
creating duplicates? (Access 2003)
Click to expand...


I think this is what you're asking for:

SELECT tblOwners.ownername, tblOwners.address
FROM tblOwners INNER JOIN (tblJunction INNER JOIN
tblJunction
ON tblJunction.lotID = tblJunction.lotID)
ON tblOwners.= tblJunction.ownerID
WHERE Year(tblLots.PurchaseDate) = 2003
GROUP BY tblOwners.ownername, tblOwners.address
 
John said:
SELECT tblOwners.*
FROM tblOwners
WHERE tblOwners.OwnerID IN
(SELECT OwnerID
FROM tblJunction
INNER JOIN tblLots
ON tblJunction.LotID = tblLots.LotID
WHERE Year(tblLots.PurchaseDate) = 2003)
Click to expand...


John, it's interesting that you chose a subquery approach.
Aside from my typo in the nested Join, I believe the nested
joins and the subquery will produce the same results. I may
be on thin ice here, but I always thought a Join would be
significantly faster than a subquery? Or does the query
planner rearrange it all into the same internal operations?
 
My query is updatable, yours is not (because of the Group By). Mine will
run faster restated like the following, but it depends on the dorky
DISTINCTROW keyword:

SELECT DISTINCTROW tblOwners.*
FROM tblOwners
INNER JOIN
(SELECT OwnerID
FROM tblJunction
INNER JOIN tblLots
ON tblJunction.LotID = tblLots.LotID
WHERE Year(tblLots.PurchaseDate) = 2003) As T2003
ON tblOwners.OwnerID = T2003.OwnerID

I try to post SQL that will a) be updatable and b) will also work in SQL
Server or Oracle or whathaveyou. (ANSI-standard syntax) The above will work
in Access only (A2000 and later).

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
I knew there had to be a good reason ;-)
All good things to keep in mind.

Thanks for the followup, John.
 
Back
Top