[Koha] sql report for bibliographic records with more than one subject

[schnydszch]

So I researched on this for the past few days, scouring the internet, only to
find partly the answer in mysql-xml documentation
(http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html), I came up with
the following:

SELECT ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]')
AS TITLE,
ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') AS
TITLE2, ExtractValue(marcxml,'//datafield[@tag="246"]/subfield[@code="a"]')
AS
TITLE3,ExtractValue(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]')
AS AUTHOR,
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][1]') AS
Subject_1,
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][2]') AS
Subject_2,
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][3]') AS
Subject_3,
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][4]') AS
Subject_4,
ExtractValue(marcxml,'//datafield[@tag="653"]/subfield[@code="a"][5]') AS
Subject_5 FROM biblioitems LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber) WHERE
biblioitems.itemtype='LAWS' ORDER BY biblio.title asc. So what this do is
query field/tag 653a - uncontrolled vocab, adding [1] will bring the first
653a tag/subject. However, it did not answer my first query of putting in 1
column all the subjects separated by a dilemeter, like this: Railroad
travel; Roads -- Surveying -- Finance. The query instead had column 1
Subject 1, column 2 Subject 2, up to Subject 5. Provided there are only up
to five subjects to each bilbliorecord, and you want each subject in one
column, this would suffice. So, I'll still try to find solution to my
original query, I'm thinking of combination of stored procedure,
substring_index (?) as what I have read so far. Or concatenate the extracted
values, but it will make the query long. That's all for now. Cheers to all!



--
View this message in context: http://koha.1045719.n5.nabble.com/sql-report-for-bibliographic-records-with-more-than-one-subject-tp5812484p5812866.html
Sent from the Koha-general mailing list archive at Nabble.com.