PJ wrote:
I am trying to select names from a list which depends on another table for their ranking. I want to retrieve all names that start with a Capital letter in last_name and ranking of 1 and any other names that have a ranking of 2 but are both related to a third table. tables: book(id,...etc.), author(id,first_name,last_name), book_author(authID,bookID,ordinal) I can not figure out how this could be done without resorting to a separate query. I would like to do it all with just 1 query; unless it is more efficient to do it with several queries? The code below works fine for ranking of 1 but I can't get the results I want. I suspect that I need to a subquery but I haven't figured out just how to formulate it. Any suggestions would be welcome. SELECT b.*, c.publisher, a.first_name, a.last_name, ab.ordinal, CONCAT(first_name, ' ', last_name) AS Author FROM book AS b LEFT JOIN book_publisher AS abc ON b.id = abc.bookID LEFT JOIN publishers AS c ON abc.publishers_id = c.id LEFT JOIN book_author AS ab ON b.id = ab.bookID LEFT JOIN author AS a ON ab.authID = a.id WHERE LEFT(last_name, 1 ) = '$Auth' // $Auth = any capital letter, as A,B,... ORDER BY $sort $dir LIMIT $offset, $records_per_page ; This returns an array with all the correct data so I can echo either Author or concat the first_name last_name fields. But I cannot figure out how to get the ordinal 2 authors.
Did you try an OR conditional in your WHERE clause similar to OR ranking = 2 ? Actually, you probably want a more complex WHERE clause similar to: WHERE (<uppercase comparison> and <rank=1>) or (<rank=2) You will need to use actual formulas for the <> expressions as appropriate. Yours, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
