OK, let's find some more constituent referenses. First run "select * from information_schema.columns where column_name = 'constituentid' ", that will show all tables that has a column named "constituentid" in it. We can then build a union (excluding some tables like constituents....) in our "not in" criteria. So, run this:
 
select distinct constituentid, displayname from constituents where constituentid not in 
(
select distinct constituentid from  
ExhVenuesXrefs
union
select distinct constituentid from  
RefConXrefs
union
select distinct constituentid from  
Makers
union
select distinct constituentid from  
LoanConXrefs
union
select distinct constituentid from  
HistConXrefs
union
select distinct constituentid from  
ExhConXrefs
union
select distinct constituentid from  
ExColConXrefs 
union
select distinct constituentid from  
ConAddrShipping
union
select distinct constituentid from  
ConAddrMailing 
union
select distinct constituentid from  
ConDates
union
select distinct constituentid from  
ConAttributeXrefs
union
select distinct constituentid from  
ObjConXrefs
union
select distinct constituentid from  
Loans
union
select distinct constituentid from  
AcqConXrefs
union
select distinct constituentid from  
ConXrefs
union
select distinct constituentid from  
TransDistributions
union
select distinct constituentid from  
TransPurchase
union
select distinct constituentid from  
Transactions
union
select distinct constituentid from  
ObjArtist
union
select distinct constituentid from  
SiteConXrefs
union
select distinct constituentid from  
ShipConXrefs
)
order by constituentid
 
Now you have narrowed it down a bit. Always check in TMS before deleting anything. I have checked some of constituents in my result and they did not have any hits i the relations meny in TMS. But why delete them?
Jonas.
 
Jonas Karlsson
Moderna Museet
Stockholm
Sweden
[log in to unmask]
 
[Jonas Karlsson]  -----Ursprungligt meddelande-----
Från: Brian Jennings [mailto:[log in to unmask]]
Skickat: den 27 november 2002 22:30
Till: Jonas Karlsson
Ämne: RE: searching for unlinked constituents
 
 
Hi Jonas.
 
I saw other replies that Ella got directly, and they all were similar you your clear step by step one (i.e. use Crystal and run a select query), and most mentioned that the example was incomplete.  The best approach is a union query, of course. One area that everyone forgot (me included the first time I looked at the problem): Venues!  That's a biggie.  
 
BJ
 
-----Original Message-----
From: Jonas Karlsson [mailto:[log in to unmask]]
Sent: Wednesday, November 27, 2002 11:24 AM
To: [log in to unmask]
Subject: SV: searching for unlinked constituents
 
 
Hello Ella and all TMSUSERS !
I'm not sure this will help you but it might do a interesting job for you, in SQL. It is not possible to execute it from within TMS but use MS SQL Query Analyser if you have it or any other similar tool, like the one in Crystal Reports. (if installed on windows it's : start>>program>> CrystalReportsTools>>CrystalSQLDesigner, click the blank with paper and then choose your linking info (ODBC - TMS?)and then choose to "enter sql statement directly") Run this query: "select distinct constituentid, displayname from constituents where constituentid not in (select distinct constituentid from conxrefs) order by constituentid". They can be linked to something elsewhere in TMS but this might point you in the right direction.
The following query will give you the number of objects related to each constituent: "SELECT constituentid, count(id) number_of_obj FROM conxrefs group by constituentid". I hope this was helpful for you.
Jonas.
 Moderna Museet, Stockholm, Sweden.
[log in to unmask]
-----Ursprungligt meddelande-----
Från: Ella Rothgangel [mailto:[log in to unmask]]
Skickat: den 26 november 2002 14:30
Till: [log in to unmask]
Ämne: searching for unlinked constituents
 
 
I'm trying to figure out a way to search for unlinked constituents (i.e. constituents who are not attached to anything anywhere and need to be deleted).  Has anyone tried this or been successful, either with an Advanced Query or a Crystal Report?
 
Thanks,
 
Ella Rothgangel
Collections Database Administrator
Saint Louis Art Museum
[log in to unmask]