getting 1 row in result set - 1toMany relationship
-
Hi,
I'm creating a query with a join between two tables that have a one to many relationship. The the essential information from the first table can't be repeated in the result set, so what we want from the second table would really be info from the first row for a specific ID. Though the info on other rows for that ID would different than the first, it could be left out of the result set.
So, something like this (not the real example, it's complicated with many other joins to other tables):
Select A.AcctNumber, A.Amount, P.PropertyAddress, P.City, P.State
from tblAccount A join tblProperty P on A.AcctNumber = P.AcctNumber
So, if a specific Account had multiple properties, there would just be one row returned in the result set, with one amount, and it would just take the first property for that Account that was in the Property table.
How can that be accomplished?
Thanks much for your assistance,
Ray
-
>> info from the first row for a specific ID.
What defines "first" ? Or is it arbitrary ? If arbitrary:
Select A.AcctNumber, A.Amount, P.PropertyAddress, P.City, P.State
from tblAccount A
Inner Join tblProperty P
On A.AcctNumber = P.AcctNumber
Inner Join
(
Select AcctNumber, Min(PropertyAddress) As FirstPropertyAddress
From tblProperty
Group By AcctNumber
) vtFirstPropertyByAccount
On (vtFirstPropertyByAccount.AcctNumber = P.AcctNumber And
vtFirstPropertyByAccount.FirstPropertyAddress = P.PropertyAddress )
-
PW, that did the trick. Thanks!!
It turns out it's not arbitrary, it has to be the 1st row the user entered in the Property table, which does have a unique ID field. So I just modified your code by putting the PropertyID in where it was needed:
Select A.AcctNumber, A.Amount, P.PropertyAddress, P.City, P.State
from tblAccount A
Inner Join tblProperty P
On A.AcctNumber = P.AcctNumber
Inner Join
(
Select AcctNumber, Min(PropertyID) As FirstPropertyID
From tblProperty
Group By AcctNumber
) vtFirstPropertyByAccount
On (vtFirstPropertyByAccount.AcctNumber = P.AcctNumber And
vtFirstPropertyByAccount.FirstPropertyID= P.PropertyID)
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply