[SOLVED] Query to locate multiple instances of MARC tag (SQL)

Query to locate multiple instances of MARC tag (SQL)

I am trying to run a query that will search the bibliographic record database and output all of the records that have more than one 020 tag according to some set parameters.

Note: the parameters are flexible. The important part is to find the records with two or more 020 tags.

Here is what I have:

select b.bib#, b.text, t.processed, i.call, i.collection, i.creation_date
from bib b, title t, item i
where b.bib# = t.bib# and t.bib# = i.bib#
    and b.tag = '020'
    and i.collection in ('PIC', 'E')
order by i.creation_date DESC, i.collection

This will display each 020 and the accompanying text, but I want it to only display records with two or more 020s. Group by bib# maybe?

Solution

I'm not sure about the exact syntax you'll need, but you could use the subquery:

                   select b.bib#
                   from bib b
                   where b.tag = '020'
                   group by b.bib#
                   HAVING COUNT(*) > 1

To identify bib# with multiple '020' entries, then either JOIN to that or use WHERE criteria with IN like:

select b.bib#, b.text, t.processed, i.call, i.collection, i.creation_date
from bib b, title t, item i
where b.bib# = t.bib# and t.bib# = i.bib#
    and b.tag = '020'
    and i.collection in ('PIC', 'E')
    and b.bib# IN (select b.bib#
                   from bib b
                   where b.tag = '020'
                   group by b.bib#
                   HAVING COUNT(*) > 1)
order by i.creation_date DESC, i.collection