PJ wrote:
I've searched the web, the tutorials, etc. with no luck and have asked
on MySql list with no luck and have even posted here with no replies.
So, let's try again:
I am trying to limit the search for books to only those that start with
"A" (does not have to be case sensitive); but within that selection
there may be a second author whose name may start with any letter of the
alphabet.
So, the real problem is to find if there is a second author in the
selection of books and to display that name.
My query shows all the books that begin with A:
$SQL = "SELECT b.*, c.publisher, a.first_name, a.last_name
FROM book AS b
LEFT JOIN book_publisher as bp ON b.id = bp.bookID
LEFT JOIN publishers AS c ON bp.publishers_id = c.id
LEFT JOIN book_author AS ba ON b.id = ba.bookID
LEFT JOIN author AS a ON ba.authID = a.id
WHERE LEFT(last_name, 1 ) = '$Auth' ";
Within the results there are some books that have 2 authors and now I have to find if
there is a second author in the results and then be able to echo the second author.
So far, I have not been able to figure out how to go about that.
Do I need to do another query to find the second author or can it somehow be incorporated into the original query? Or can it be done with a UNION ?
Union won't help because you don't know the results from the first query
to put into the union.
You could do it in one query but whether it's worth it is up to you to
decide & test. I've removed the "book_publisher" stuff to make it easier
to understand.
Step 1:
get the books the author has written. You don't need to join all of the
tables here because you only want the bookid's.
select
bookid
from
book_author ba
inner join
authors a on (ba.authorid=a.id)
where
left (last_name, 1) = 'A'
Step 2:
Get all authors for those books:
select
ba.*
from
book_author ba
inner join
authors a on (ba.authorid=a.id)
where
ba.bookid in
(
select
bookid
from
book_author ba
inner join
authors a on (ba.authorid=a.id)
where
left (last_name, 1) = 'A'
);
They should be inner joins, not left joins.
If you need to join to other tables (book_publishers etc) don't do it in
the inner query, do it in the outer (select ba.* stuff).
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
