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
);
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
