Record set of bibs with MARC tag containing certain text

Polaris
1

Hi all,

What would be the best way to create a record set of bibs that all have a certain MARC tag containing certain text? I’m sure it’s super simple but I can’t figure it out.

Thanks!

2

This would be a good starting point:

3

Here is how you send the results from the Find Tool into a record set.

4

Thanks @wesochuck. That worked perfectly!

1 Like
5

How would I add that the bibs also do NOT have a certain tag containing certain text? Thanks again.

6

They have the tag but not the text or they don’t have the tag? Or they DO contain a certain tag text but also do NOT contain other tags/text.

Can you give examples of specifically records that would/would not match?

7

Hi Wes. For this particular query I’m looking for bibs that have an 090 containing “on order” and do not have a 500 containing “on order”. Because I will be adding a 500 “on order” to the ones missing it. So it would find the bib below but not the one below that.

=LDR 00463nam 22001695a 4500

=003 OAUW

=008 250328s2025\\xx\\\d\\\000\0\eng\

=020 \$a9780063433434$c46.00

=040 \$aCaOAUW$beng$cCaOAUW

=090 \$aON ORDER

=100 1$aBergreen, Laurence.

=245 10$aJules Verne and the Invention of the Future

=260 \$a$bHarperCollins Publishers$c2025

=263 \$a202507

=300 \$a464 p.

=541 \$aWhitehots

=970 \$lm$q1$fM100ARPlpnm$canl$p46.00$m18$n

but not this one:

=LDR 00349nam a22001455a 4500

=008 250402s2010 xx r 000 0 eng

=020 $a9781439181782$c36.99

=090 $aON ORDER

=100 1 $aByrne, Rhonda

=245 00$aPower.

=260 $a$bAtria Books$c2010

=300 $a272 p.

=500 $aON ORDER

=541 $aWhitehots

=970 $lm$q1$fM100anm$canb$p36.99$m3

Thanks!

8

So, if a record were to have a 500 tag with something ELSE in it (other than ON ORDER), would you want it to show up?

9

Yes please. Anything else in a 500 is fine. Thanks.

10

Here is some untested code from Gemini that looks like it could do the trick - for the benefit of others who might reference this in the future, please let us know if this works:

SELECT DISTINCT -- Use DISTINCT in case a record has multiple 090 $a 'ON ORDER' tags
    bt.BibliographicRecordID
FROM
    BibliographicTags bt
INNER JOIN
    BibliographicSubfields bs ON bt.BibliographicTagID = bs.BibliographicTagID
WHERE
    bt.TagNumber = 090
    AND bs.Subfield = 'a'
    AND bs.Data = 'ON ORDER' -- Condition 1: Must have 090 $a 'ON ORDER'
    AND NOT EXISTS ( -- Condition 2: And must NOT have a 500 $a 'ON ORDER'
        SELECT 1 -- Select anything; we only care if a row exists
        FROM BibliographicTags bt_exclude
        INNER JOIN BibliographicSubfields bs_exclude ON bt_exclude.BibliographicTagID = bs_exclude.BibliographicTagID
        WHERE
            bt_exclude.BibliographicRecordID = bt.BibliographicRecordID -- Link to the outer query's record
            AND bt_exclude.TagNumber = 500
            AND bs_exclude.Subfield = 'a'
            AND bs_exclude.Data = 'ON ORDER'
    );
11

Hi Wes. Yes, it worked perfectly. Thanks!

1 Like