Hello I have a SQL search that will pull all bibliographical records with a particular field. However, it pulls everything! Would anyone happen to have a SQL bib search that would pull 490 fields where I can also narrow down by collection?
Without seeing your current query, I would probably do something like this to narrow it by collection:
select * from BibliographicRecords with (nolock)
where bibliographicrecordid in (select circitemrecords.AssociatedBibRecordID from circitemrecords with (nolock) where circitemrecords.AssignedCollectionID=[your collection ID here])
It’s more efficient to join the circitemrecords table (6 seconds vs. 9 seconds for our largest collection) but it causes problems with returning duplicate results that I wasn’t sure how to resolve without knowing which columns you were pulling.
This is my current query:
select bibliographicrecordid as recordid
from BibliographicTags where TagNumber = 490
I am very new to SQL. Is there an easy way to add a collection search to the above?
Thank you!
Maybe something like this? Just add that third line below onto your existing query? From your query I’m assuming you’re doing an SQL search in the find tool?
select bibliographicrecordid as recordid
from BibliographicTags where TagNumber = 490
and bibliographicrecordid in (select circitemrecords.AssociatedBibRecordID from circitemrecords with (nolock) where circitemrecords.AssignedCollectionID=655)
I’d also recommend specifying that you want only “distinct” (or unique) results if you’re looking for bib records:
select distinct(bibliographicrecordid) as recordid
from BibliographicTags where TagNumber = 490
and bibliographicrecordid in (select circitemrecords.AssociatedBibRecordID from circitemrecords with (nolock) where circitemrecords.AssignedCollectionID=655)
1 Like