Hi Rizwan You wrote:
I am try to write a query in which all fields from items, biblio, bilioitems tables are required to be shown. But I am facing difficulties to show subject heading and classification number. I also tried extractvalue() function to show subject heading but error displayed. Has anyone the solution of this issue?
this is the query
SELECT * FROM(SELECT items.dateaccessioned, items.barcode, items.itemcallnumber, biblio.title, biblio.author, biblioitems.publishercode, (SELECT ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]')) AS Subject FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>>) AS t WHERE Subject LIKE concat('%',<<Subject>>,'%') ORDER BY dateaccessioned DESC
You wrote you need "a query in which all fields from items, biblio, bilioitems tables are required to be shown". You can achieve this with a query as follows (old SQL syntax, I don't like the new one): SELECT * FROM biblio, biblioitems, items WHERE biblioitems.biblionumber=biblio.biblionumber AND items.biblioitemnumber=biblioitems.biblioitemnumber I didn't try to find out about the "difficulties to show subject heading and classification number". However, there are two things at least: 1. You cannot have a query with two FROM clauses like "SELECT * FROM (SELECT ... SELECT (...)) AS Subject FROM items - this is definitely incorrect syntax, whatever you try to achieve. 2. Also it is not possible to have "AS t" in your first where clause. I recommend to start with a simple query, then build it up, always respecting the correct SQL syntax. Hope this helps. Best wishes: Michael -- Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz T 0041 (0)61 261 55 61 · E mik@adminkuhn.ch · W www.adminkuhn.ch