Re: difficult select problem

PJ wrote:

Hi Jim, Sorry I could not gat back to you on your suggestion. I've been under the weather for a couple of days but am almost over it. Your suggestion does not work... yet. I'll insert comments & questions below... Jim Lucas wrote:

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 ? Please help.

... and we begin ... $SQL = "SELECT b.*, c.id AS publisher_id, c.publisher, a.id AS author_id, a.first_name, a.last_name

        FROM book AS b
        INNER JOIN book_publisher as bp ON b.id = bp.bookID
        INNER JOIN publishers AS c ON bp.publishers_id = c.id
        INNER JOIN book_author AS ba ON b.id = ba.bookID
        INNER JOIN author AS a ON ba.authID = a.id
        WHERE b.id IN (
                SELECT  b.id
                FROM    book AS b
        INNER JOIN book_author AS ba ON b.id = ba.bookID
                INNER JOIN author AS a ON ba.authID = a.id
                WHERE   a.last_name LIKE '{$Auth}%'
        )";

A written, the code does not produce a result. Questions: 1. why c.id AS publisher_id and a.id AS author_id? I do not see a reference to these in the code 2. why "a.last_name LIKE '{$Auth}%'? What does that change from LEFT(last_name, 1) = '$Auth'? the LEFT clause will give me all authors whose names begin with A; I don't care what the rest of the string may be - the authors' last names always starts with a capital letter. 3. What were you expecting to get as a result from the code? It looks to me like the results will only give all authors whose names begin with A. And that I am already getting with the code as I have written. Sorry for my ignorance, but that is why I am asking for help. 4. So far, no one has used the book_author.ordinal to select the author ; perhaps I have not been clear on that as that is what I use to determine if there are more than 1 author. Ordinal 1 is for all authors who are unique for a book; ordinal 2 is for authors who are the second author for a book.

Thanks for explaining the ordinal significance. Does it really make a difference if the ordinal is 1 or 2? Does 1 indicate the primary author and the 2 indicate supporting authors??? If a book has three authors and all three had an ordinal of 2, would it make any difference then if one was 1 and the remaining two were marked 2? If it makes no difference, the the column is pointless. If it does make a difference (identifying one as 1 and the remaining as 2), then keep it and we will deal with that later. But, all this can be done without that column. Read my other email that I about to send, responding to a different part of this thread.

Now, if my code is not too messy or cumbersome (I refer to your e-mail of March 15) then what I really need is a way to save the book.id from my query to a $tring and then use that to select the book in another query that will give me the second author. I am wondering how that is done; I have already used this in retrieving arrays but the id is changed in a simple $bk_id = $result - damn, I forget where I found that in the manuals. I used it in another situation like this: $catvar = array_values($category[$categoryID]); $cat = $catvar[1]; $catvar was ids like 9, 6, 17, etc. and assigning it to $cat ranged them as 1, 2, 3, 4, etc. (which is what I want to avoid) Then I should be able to do a "SELECT CONCAT_WS(" ", first_name, last_name) as Author2 FROM author a, book b, book_author ba WHERE $bk_id = ba.bookID && ba.authID = a.id" or "SELECT CONCAT(first_name, " ", last_name, book_author.ordinal) as Author2 FROM author a, book b, book_author ba WHERE $bk_id = ba.bookID && ba.authID = a.id" I probably have something wrong in the code; I haven't tried it as I am not sure how to retrieve the $bk_id. And the CONCAT with the ordinal, I can filter the 1 or the 2 when echoing. I must be really long-winded but this is a long and complicated exercise for my little brain. :'(

Ok, with those changes made, you will now see that your result set is going to have duplicate entries for each book. Well, almost duplicate... Basically what you need to realize is that you are talking about row counts compounding upon themselves... I will try and explain... if you had ten books with a total of 16 unique authors and 8 unique publishers, you would end up with a result set (without any WHERE clause) that was 10 x 16 x 8 = 1280 rows. now, say that you put a limit of "authors last name must start with and 'A'" on the select (like you have) say that three of the authors match that and of those three, they had 4 books that they have written and those 4 books were published by 2 unique publishers. You would end up with 3 x 4 x 2 = 24 rows in result set. I would consider handling all the above as I have stated, but you will need to add some sorting of the data in PHP to make sense of it all. something like the following should do. (completely untested!!!) typed right in the client here... $book_information = array(); if ( ( $results = mysql_query($SQL, $db) ) !== false ) {

    while ( $row = mysql_fetch_assoc($results) ) {
        $book_information[$row['id']]['title'] = $row['title'];

                # NOTICE: this row is here so you can replace it with

other information from the book table

        $book_information[$row['id']]['...'] = $row['...'];

        $book_information[$row['id']]['authors'][$row['author_id']] =

array('first_name' => $row['first_name'],

                                                                                   'last_name' => $row['last_name']);
       $book_information[$row['id']]['publishers'][$row['publisher_id']] =

$row['publisher'];

    }

} Do a print_r($book_information) and you should get an understanding of what is happening. Jim Lucas

-- Jim Lucas "Some men are born to greatness, some achieve greatness, and some have greatness thrust upon them." Twelfth Night, Act II, Scene V by William Shakespeare