asked on

ORACLE: Able to show multiple 'matches' of one attribute in one row without knowing how many matches there are

Hi
I've got six relations as shown below (i've only included relevant attributes). Each book could have multiple authors and multiple subjects. I need to be able to display all of a books details in one row, searched by callNo and including the display of title, author name(s), subject name(s) and publisherName. I have worked out how to do it if I know exactly how many authors and subjects it has (see below, this one has two authors and two subjects), but I can't work out what to do to get it to work without knowing the number of authors or subjects.
Thankyou Roxanne

THESE ARE MY TABLES
book( call_No, title, pub_id)
publisher(pub_Name)
subject(sub_id, sub_name)
bookSubject(call_no, sub_id)
authorOfBook(call_no, auth_id)
author(auth_id, auth_name)

THIS IS MY SQL STATEMENT
SELECT b.call_No, b.title, p.pub_Name,
a1.auth_Name || ', '||a2.auth_Name, s1.sub_Name || ', '||
s2.sub_Name
FROM author a1, author a2
book b, publisher p, subject s1, subject s2
authorOfBook ab, bookSubject bs
WHERE
b.call_No = '300A' AND
bs.call_No = b.call_No AND
ab.call_No = b.call_No AND
s1.sub_Id in ( select bs.sub_Id from bookSubject bs WHERE bs.call_No = '300A') AND
s2.sub_Id in ( select bs.sub_Id from bookSubject bs WHERE bs.call_No = '300A') AND
s1.sub_Id < s2.sub_Id AND
b.pub_Id = p.pub_Id AND
a1.auth_Id in ( select ab.auth_Id from authorOfBook ab WHERE ab.call_No = '300A') AND
a2.auth_Id in ( select ab.auth_Id from authorOfBook ab WHERE ab.call_No = '300A') AND
a1.auth_Id < a2.auth_Id
GROUP BY b.call_No, b.title, p.pub_Name,
a1.auth_Name || ', '||a2.auth_Name, s1.sub_Name || ', '||
s2.sub_Name

Arthur_Wood

you should have additional tables BookAuthor (Call_No, Auth_iD) and BookSubject (Call_no, Sub_ID) to resolve the potential for Many-to-many relationships between books and authors (one book can have 1 or more authors, and any one author may have written 1 or more books), and also between Books and Subjects (a boiok can have 1 or more subjects, and any given subject may be addressed by 1 or more books) Then you can have as many authors as needed for any one book, and as many subjects as needed for any one book.

AW

RoxanneMcCafferty

ASKER

That is why there is an author table and a authorOfBook table and a sujbect table and a bookSubject table. I can have as many as I want in the tables, the issue is the getting a query to show all of a single books details so it shows like below in one row:

callNo title pub_name author1, author2 etc... subject1, subject2, subject3 etc...
300A Hello Johnstone Rose Camwell, Sarah Camwell History, Politics, Religion

Arthur_Wood

the two tables that I suggested, you already have, so what you need to do is:

Select Book.Title, author.name, subject.sub_name from book join AuthorofBookon book.call_no = authorOfBook.call_no join author on authorofbook.auth_id = author.auth_id join booksubject on book.call_no = booksubject.call_no join subject on booksubject.sub_id = subject.sub_id

AW

Arthur_Wood

you probably can't get all of the details on one line, without resorting to s Stored Procedure.

AW

Arthur_Wood

why do you want all the details on one line?

ASKER CERTIFIED SOLUTION

izblank

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RoxanneMcCafferty

ASKER

Thanks for your responses arthur and izblank

A friend of mine solved my problem in a very similar way to izblank's solution and it works well, the code I used for anyone who searches this question was as follows:

CREATE OR REPLACE TYPE string_list_t AS TABLE OF VARCHAR2(50);
/
CREATE OR REPLACE
Function CONCAT_LIST
( lst IN string_list_t, separator varchar2)
RETURN VARCHAR2 IS
ret varchar2(400) := '';
BEGIN
IF lst.COUNT != 0 THEN
FOR j IN 1..lst.LAST LOOP
ret := ret || lst(j) || separator ;
END LOOP;
END IF;
RETURN ret;
END;
/

SELECT b.call_no, b.title, p.pub_name as publisher, CONCAT_LIST(authors, ', ') as authors, CONCAT_LIST(subjects, ', ') as subjects
FROM publisher p, book b, (SELECT b.call_no,
CAST(MULTISET(SELECT auth_name
FROM authorOfBook, author
WHERE authorOfBook.call_no = b.call_no
AND authorOfBook.auth_id = author.auth_id) AS string_list_t) authors,
CAST(MULTISET(SELECT sub_name
FROM bookSubject, subject
WHERE bookSubject.call_no = b.call_no
AND bookSubject.sub_id = subject.sub_id) AS string_list_t) subjects
FROM book b
WHERE b.call_no = '300A'
GROUP BY b.call_no )
WHERE b.call_no = '300A'
AND p.pub_id = b.pub_id
;