Re: formulate nested select

Chris wrote:

PJ wrote:

I cannot find anything on google or the manuals/tutorials that gives some kin of clear explanation of how to so nested selects with where or whatever. I have three tables: books, authors and book-authors. I need to retrieve only those books whose author's names begin with A. I have tried several maniipulations of where and select with select subqueries and I cannot get results from the queries. For example "SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID && b.id = c.bookID ....<snip>

Not really a php question :P You don't need a subquery for this. You can join all of the tables together and just use the where clause to cut down your results, but I'll give an example of both. select * from books b inner join book_authors c on (b.id=c.bookId) inner join authors a on (a.id=c.authorId) where left(a.last_name = 'A');

correct me if I'm wrong, but did you use the left() function in-correctly? The documentation shows a different way to use it then you describe. Something more like the following: WHERE LEFT(a.last_name, 1) = 'A'; But that would be case-sensitive... So, something like this would work better IMHO WHERE UPPER(LEFT(a.last_name, 1)) = 'A'; or WHERE a.last_name ILIKE 'A%'; would do the trick

or select * from books b inner join book_authors c on (b.id=c.bookId) where c.authorId in (

    select id from authors where left(last_name='A')

Again... SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A') but yet again, case-sensitive... SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or SELECT id FROM authors WHERE last_name ILIKE 'A%' would do the trick

);