Bob McConnell wrote:
> From: PJ
>> First, let me thank you all for responding and offering suggestions. I
>> appreciate it and I am learning things.
>> However, it looks like my message is not getting across:
>> The problem is not to retrieve only the authors whose last names begin
>> with A:
>
> Actually, it appears you simply don't like the accurate answers you have
> been given.
First, let me say that I am pretty fresh to all this.
Second, I believe I can get the authors with several more queries as I
have done for listings of all the books and by categories, but I am
trying to limit the queries thinking that that will speed up the data
retrieval.
Third, I have tried the answers proposed and have not been able to make
them work up to now. Will continue... (it's a bit difficult at the
moment as I do not have my entire head about me - nasty cold)
Fourth, I don't see any reference to the book_author.ordinal which seems
important to me to determine the placement of the authors.
>
>> 1) which books have a second author?
>> 2) who is the second author ?
>> This is determined by table book_author column ordinal (which can be 1
>> or 2) - if there is only 1 author for a book then there is no ordinal
> 2
>> linked to book_author bookID and authID.
>
> There is no way to do that in a single select. You need to have at least
> two and possibly three queries to answer your question. First you get a
> list of authors where their name begins with 'A'. Then you use that
> result to select a list of all books with more than one author. Then you
> can use that result to select all authors for them.
I am a bit confused as to how to use the result in further queries.
And I don't understand what the difference is in using:
$SQL = "SELECT b.id FROM book AS b
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' ";
instead of:
$SQL = "SELECT b.id
FROM book b, book_author ba, author a
WHERE b.id = ba.bookID && a.id = ba.authID && LEFT(a.last_name,
1) = '$Auth' ";
It seems simpler without the JOINs; but is there an advantage or is that
merely a personanl choice of several possibilities?
>
> Everyone has told you this requires processing beyond what SQL can
> provide. Why is that a problem?
It's not a problem. I'm just trying learn what can and cannot be done.
For instance, I could just put new columns in the db for author1 and
author2. But I suspect it might not be as efficient with a great many
books (like thousands).
>
> Bob McConnell
>
--
unheralded genius: "A clean desk is the sign of a dull mind. "
-------------------------------------------------------------
Phil Jourdan --- pj@xxxxxxxxxxxxx
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
