Hi there again guys!

 

I have the following table layout:

 

BOOK - b_id, b_title, ...

 

PERSON - p_id, p_name, ...

 

PERSON_BOOK - id, book_id, person_id, contributor_type_id

 

CONTRIBUTOR_TYPE - id, type

 

 

Now, each book (BOOK) can have several contributors (PERSON), and each person (PERSON) can contribute to several books (BOOK). A person can be an Author of the book, Translator, etc (CONTRIBUTOR_TYPE). These contributor types are sorted in order of importance for a book. For instance the author is always most important and each book should have a "main contributor". PERSON_BOOK links each person to a book giving them a type (a person can be an author of one book and a translator of another)

 

I am having difficulty when it comes to retrieving and sorting the data for each book. At the minute, let's say I can sort by book title, and price no problem. The way I'm doing this is probably wrong and inefficient and is probably why sorting by main contributor is not working for me. What I want to do is sort by main contributor (This is usually the author, but if the author is not listed then it would be the highest rated CONTRIBUTOR_TYPE associated with the book)

 

At the minute my sql query looks like this

 

SELECT * FROM book AS b, person AS p, person_book AS pb
WHERE b.b_id=pb.book_id AND p.p_id=pb.person_id 
ORDER BY b_price DESC, pb.contributor_type_id ASC

 

After running that query I simply loop through each entry. After a book is added the b_id is added to an array, and I simply ignore all other entries if the b_id is already in that array.

 

When I try to sort by main contributor, the main contributor for each book becomes whoever's name starts with the lowest letter. I can't link a main contributor to a book and then sort based on that.

 

Can any one help me?

 

Thanks all

Didn't read properly but I believe you're looking to use sql joins?

Didn't read properly but I believe you're looking to use sql joins?

 

I've been reading up on joins but can't find a suitable solution. Can anybody else help?