Re: difficult select problem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Gentlemen,

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:

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.

The structure of the db:

book
   id
   title
   sub_title
   descr
   comment
   bk_cover
   copyright
   ISBN
   language
   sellers

author
   id
   first_name
   last_name
  
book_author
   authID
   bookID
   ordinal

categories and publishers are not realy relevant here...

The code I have:

$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' ";

(PLEASE LET ME KNOW IF THERE IS SOMETHING WRONG WITH THE CODE)
It gives me these results:

*array*
6 =>
*array*
'id' => string '6' /(length=1)/
'title' => string 'Nubia.' /(length=6)/
'sub_title' => string 'Corridor to Africa' /(length=18)/
'descr' => string '' /(length=0)/
'comment' => string '' /(length=0)/
'bk_cover' => string '' /(length=0)/
'copyright' => string '1977' /(length=4)/
'ISBN' => string '0691093709' /(length=10)/
'language' => string 'en' /(length=2)/
'sellers' => string '' /(length=0)/
'publisher' => string 'Princeton University Press' /(length=26)/
'first_name' => string 'William Yewdale' /(length=15)/
'last_name' => string 'Adams' /(length=5)/

This is the first of 17 books and it would be sufficient if I were not
so damned demanding.
I know that there are several books in the list that have 2 authors. At
least 1 whose last name begins with A is an ordinal=2 (which is not
important when it comes to displaying) and there are a couple where
there are 2nd authors whose names begin with other letters of the
alphabet. It is these that I am targeting.
My question is:

How do I filter my query to weed out these renegades?

Can it be done using a concat as Author1 and another concat as Author2
(in place of the first_name and last_name joins ?
Or do I do another query just for the outlaws?



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux