Different components of 1-M in single query

[rbruder]

Morning folks, my first post here. I'm hoping it'll be an easy one for those more experienced than I.

Table 1: Parcels
Parcel ID

Table 2: Ownership
Parcel ID
Person ID
OwnerStatus ('Current Owner','Current Co-Owner')

Table 3: Person
Person ID
First Name
Last Name

Parcels & Ownership have a 1-M relationship since a single parcel can have both an Owner and one or more Co-Owners. ParcelID is the key for the relate.

Ownership & Person have a M-1, using PersonID.

This is a newly designed database and I'm trying to run a single query on it to produce a single table that matches the design of the agency's previous legacy DBF structure. That structure has the following select fields (among others):

PARCELID
FIRSTNAME
LASTNAME
CO_OWNER

In my SQL statement, then, I'm using a WHERE statement (WHERE ((([Ownership]![OwnerStatus])='current owner')) to populate the LastName field and only pull a single 'current owner' record.

My question is whether it is possible to, in the same query, to also populate the co-owner field by similarly using an INNER JOIN but this time limiting to only co-owners. In addition to trying limit this in a single Select statement, I've tried using a SubQuery without success.

Thanks in advance for your help,

Ron

 

[rac2]

SELECT ParcelID, FirstName, LastName, Ownerstatus
FROM Parcels
JOIN Ownership ON Ownership.PARCELID=Parcels.PARCELID
JOIN Person ON Ownership.PersonID=Person.PersonID

will list all of the owners of every parcel. If there is a single owner it will list the parcel once with the name of the owner and "Current Owner"; if there are multiple owners it will list the parcel multiple times, once for each co-owner, their names, and "Current Co-owner". There is no need to add a WHERE condition.

Is that what you need? Or is it that you want the first and last name of one owner under FIRSTNAME and LASTNAME, and the name of any other co-owner under CO-OWNER? What if there are three co-owners?

Or is it that you want to list a parcel only once, show the owner's name if there is a single owner, or, if there are co-owners show arbitrarily one owner's name with a notation that there is at least one co-owner?

 

[rbruder]

rac2

Thanks for your input. It is the last design you note that I'm trying to emmulate. In the end, I need a single record for each parcel with a first and last name pulled from the join, and a co-owner, if it exists, written to the co-owner field.
Ron

 

[rac2]

Let's start with the co-owners' names.

SELECT ParcelID, PersonID, FirstName + LastName AS "Co_ownerName"
FROM Ownership 
JOIN Person ON Ownnership.PersonID=Person.PersonID
WHERE OwnerStatus='Current Co-Owner'

And, for convenience, create a view from that query and call the view CoOwners.

Then go for the complete result.

SELECT pa.ParcelID, pe.FirstName, pe.LastName, co.Co_ownerName
FROM Parcels pa
JOIN Ownership o ON pa.ParcelID=o.ParcelID
JOIN Person pe ON o.PersonID=pe.PersonID
LEFT JOIN CoOwners co ON pa.ParcelID=co.ParcelID
             AND co.PersonID <> o.PersonID

And clean that up a bit

SELECT pa.ParcelID, pe.FirstName, pe.LastName, ISNULL(co.Co_ownerName, 'None')
FROM Parcels pa
JOIN Ownership o ON pa.ParcelID=o.ParcelID
JOIN Person pe ON o.PersonID=pe.PersonID
LEFT JOIN CoOwners co ON pa.ParcelID=co.ParcelID
             AND co.PersonID <> o.PersonID

I don't know for sure that this will work. At least it may give you some ideas. Also there is still the issue of three or more owners. I think these will generate multiple rows for such a parcel.