External Email - Exercise Caution
Thank you, Kathleen! I won't try your elegant looking query just now because my brains hurt. I did finally get what I needed (Including Object and Constituent info). This is my query:

Select Distinct ObjComponents.ObjectID, Objects.ObjectNumber, Constituents.DisplayName, ObjTitles.Title, Objects.Dated, Objects.Medium,
Objects.ObjectCount, Locations.LocationString, ObjLocations.EnteredDate
from ObjComponents
inner join ObjLocations on (ObjLocations.ObjLocationID = ObjComponents.CurrentObjLocID)
inner join Objects on (ObjComponents.ObjectID = Objects.ObjectID)
inner join ObjTitles on (Objects.ObjectID = ObjTitles.ObjectID)
inner join Locations on (ObjLocations.LocationID = Locations.LocationID)
inner join ConXrefs on (Objects.ObjectID = ConXrefs.ID)
inner join ConXrefDetails on (ConXrefDetails.ConXrefID = ConXrefs.ConXrefID)
inner join Constituents on (ConXrefDetails.ConstituentID = Constituents.ConstituentID)
where ObjLocations.LocationID = 2830
and ObjTitles.DisplayOrder = 1
and ConXrefs.DisplayOrder = 1
and ConXrefDetails.RoleTypeID = 1

best!
d

David Lowe | The New York Public Library
Specialist III, Photography Collection


On Tue, Apr 16, 2024 at 11:57 AM Kathleen Forrest <[log in to unmask]<mailto:[log in to unmask]>> wrote:
External Email - Exercise Caution
Hi David, is this what you're looking for?

SELECT top 10 O.ObjectNumber, C.ComponentNumber, OL.TransDate, L.LocationString, CR.CrateNumber from Objects O
LEFT JOIN ObjComponents C on C.ObjectID = O.ObjectID
LEFT JOIN ObjLocations OL on OL.ObjLocationID = C.CurrentObjLocID
LEFT JOIN Locations L on L.LocationID = OL.LocationID
LEFT JOIN Crates CR on CR.CrateID = OL.CrateID

Best,

Kathleen Huang Forrest
Collections Database Administrator

Fine Arts Museums of San Francisco
de Young \ Legion of Honor
50 Hagiwara Tea Garden Drive
San Francisco, CA 94118
c 682.365.8457
p 415.750.8949
[log in to unmask]<mailto:[log in to unmask]>

On Tue, Apr 16, 2024 at 7:59 AM David Lowe <[log in to unmask]<mailto:[log in to unmask]>> wrote:
External Email - Exercise Caution
Hi all,
I'm pretty confounded by the tables involved in querying Object Locations. I managed to get Home Location, but I'm needing Current with the date  of the change. Has anybody done this?

Best,
David

David Lowe | The New York Public Library
Specialist III, Photography Collection

To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:

signoff TMSUSERS

// eoj

You will receive a confirmation that your subscription has been removed.

To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:

     signoff TMSUSERS

     //  eoj


You will receive a confirmation that your subscription has been removed.